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.