Hi Karl and Jeff,
On 26 June 2017 at 22:22, Jeff Janes <jeff.ja...@gmail.com> wrote:
> Be warned that "explain (analyze)" can substantially slow down and distort
> this type of query, especially when sorting. You should run "explain
> (analyze, timing off)" first, and then only trust "explain (analyze)" if
> the overall execution times between them are similar.
>
Thanks, I didn't realise that. I will use TIMING OFF from now on.
On 26 June 2017 at 21:32, Karl Czajkowski <kar...@isi.edu> wrote:
> > I created the index starting with date and it did make a big
> > difference: down to 10.3 seconds using a bitmap index scan and bitmap
> > heap scan (and then two hash joins as before).
>
> By the way, what kind of machine are you using? CPU, RAM, backing
> storage?
>
> I tried running your original test code and the query completed in
> about 8 seconds, and adding the index changes and analyze statement
> brought it down to around 2.3 seconds on my workstation with Postgres
> 9.5.7. On an unrelated development VM with Postgres 9.6.3, the final
> form took around 4 seconds.
>
This is very interesting. I'm using a powerful box:
- HP ProLiant DL580 G7, Xeon(R) CPU E7- 4850 @ 2.00GHz * 80 cores, 128
GB RAM, hardware RAID, 3.6 TB SAS array.
total used free shared buff/cache
available
Mem: 125G 2.2G 834M 30G 122G
91G
Swap: 9.3G 98M 9.2G
And disk I/O is fast:
$ dd if=/dev/zero of=/local/tmp/bigfile bs=1M count=100k
107374182400 bytes (107 GB) copied, 234.751 s, 457 MB/s
But your question let me to investigate and discover that we were compiling
Postgres with no optimisations! I've built a new one with -O2 and got the
time down to 3.6 seconds (EXPLAIN with either TIMING OFF or BUFFERS,
there's no material difference).
And again, vacuum your tables. Heap fetches aren't cheap.
>
Sorry, I don't understand, why does VACUUM help on a table with no deleted
rows? Do you mean ANALYZE?
> > work_mem = 100MB
>
> Can you give it more than that? How many simultaneous connections do you
> expect?
>
Yes, I can and it does help! By increasing work_mem to 200 MB, I managed to
convert the external merge sort (on disk) to a quicksort in memory, and
reached 3.3 seconds.
The cartestian join is slightly faster at 3.0 seconds, but not enough to be
worth the overhead of creating the join table. I still wish I understood
why it helps.
Jeff, thanks for the explanation about hash joins and sorting. I wish I
understood why a hash join wouldn't preserve order in the first table even
if it has to be done incrementally, since I expect that we'd still be
reading records from the first table in order, but just in batches.
Other possible rewrites to try instead of joins:
>
> -- replace the case statement with a scalar subquery
>
> -- replace the case statement with a stored procedure wrapping that
> scalar subquery
> and declare the procedure as STABLE or even IMMUTABLE
>
> These are shots in the dark, but seem easy enough to experiment with and
> might
> behave differently if the query planner realizes it can cache results for
> repeated use of the same ~100 input values.
I hit a jackpot with jsonb_object_agg, getting down to 2.1 seconds (2.8
with BUFFERS and TIMING <https://explain.depesz.com/s/uWyM>):
explain (analyze, timing off)
with metric as (select jsonb_object_agg(id, pos) AS metric_lookup from
metric_pos),
asset as (select jsonb_object_agg(id, pos) AS asset_lookup from
asset_pos)
SELECT metric_lookup->id_metric AS pos_metric, asset_lookup->id_asset AS
pos_asset, date, value
FROM metric_value, metric, asset
WHERE date >= '2016-01-01' and date < '2016-06-01'
AND timerange_transaction @> current_timestamp
ORDER BY metric_value.id_metric, metric_value.id_asset, date;
Which is awesome! Thank you so much for your help, both of you!
Now if only we could make hash joins as fast as JSONB hash lookups :)
Cheers, Chris.
