Hi Karl and Jeff,
On 26 June 2017 at 22:22, Jeff Janes <jeff.janes@xxxxxxxxx> 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 <karlcz@xxxxxxx> 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 availableMem: 125G 2.2G 834M 30G 122G 91GSwap: 9.3G 98M 9.2G
And disk I/O is fast:
$ dd if=/dev/zero of=/local/tmp/bigfile bs=1M count=100k107374182400 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 = 100MBCan 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):
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, valueFROM metric_value, metric, assetWHERE date >= '2016-01-01' and date < '2016-06-01'AND timerange_transaction @> current_timestampORDER 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.
