1.) Server setting
memory: 32960116kB = 32GB
2.) Current Postgresql configuration settings of note in my environment.
enable_hashjoin=off
work_mem = 16MB
#random_page_cost-4.0 <- default
maintenance_work_mem=256MB
shared_buffers = 8GB
serverdb=# explain analyze select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.ID where tr1.ALGORITHM='SMAT';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=5714258.72..5714258.73 rows=1 width=0) (actual time=54402.148..54402.148 rows=1 loops=1)
Nested Loop (cost=0.00..5714253.25 rows=2188 width=0) (actual time=5.920..54090.676 rows=1481710 loops=1)
-> Index Scan using idx_SARS_ACTS_run_algorithm on SARS_ACTS_run tr1_ (cost=0.00..32.71 rows=442 width=8) (actual time=1.423..205.256 rows=441 loops=1)
Index Cond: ((algorithm)::text = 'SMAT'::text)
-> Index Scan using idx_SARS_ACTS_run_id_end_time on SARS_ACTS this_ (cost=0.00..12874.40 rows=4296 width=8) (actual time=749..121.125 rows=3360 loops=441)
Index Cond: (SARS_RUN_ID=tr1_.ID)
Total runtime: 54402.212 ms <- 54 seconds
(7 rows)
3.) Setting the recommended parameters
serverdb=# set work_mem='500MB';
SET
serverdb=# set random_page_cost=1.2;
SET
serverdb=# explain analyze select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.ID where tr1.ALGORITHM='SMAT';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1754246.85..1754246.86 rows=1 width=0) (actual time=1817.644..1817.644 rows=1 loops=1)
Nested Loop (cost=0.00..1754241.38 rows=2188 width=0) (actual time=0.135..1627.954 rows=1481710 loops=1)
-> Index Scan using idx_SARS_ACTS_run_algorithm on SARS_ACTS_run tr1_ (cost=0.00..22.40 rows=442 width=8) (actual time=0.067..0.561 rows=441 loops=1)
Index Cond: ((algorithm)::text = 'SMAT'::text)
-> Index Scan using idx_SARS_ACTS_run_id_end_time on SARS_ACTS this_ (cost=0.00..3915.12 rows=4296 width=8) (actual time=0.008..2.972 rows=3360 loops=441)
Index Cond: (SARS_RUN_ID=tr1_.ID)
Total runtime: 1817.695 ms 1.8 seconds <- very good response time improvement
(7 rows)
4.) Now toggling the enable_hashjoin, I suspect the plan is cached, so these results may be suspect.
serverdb=# set enable_hashjoin=on;
SET
serverdb=# explain analyze select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.ID where tr1.ALGORITHM='SMAT';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=1754246.85..1754246.86 rows=1 width=0) (actual time=1606.683..1606.683 rows=1 loops=1)
Nested Loop (cost=0.00..1754241.38 rows=2188 width=0) (actual time=0.136..1442.463 rows=1481710 loops=1)
-> Index Scan using idx_SARS_ACTS_run_algorithm on SARS_ACTS_run tr1_ (cost=0.00..22.40 rows=442 width=8) (actual time=0.068..0.591 rows=441 loops=1)
Index Cond: ((algorithm)::text = 'SMAT'::text)
-> Index Scan using idx_SARS_ACTS_run_id_end_time on SARS_ACTS this_ (cost=0.00..3915.12 rows=4296 width=8) (actual time=0.007..2.659 rows=3360 loops=441)
Index Cond: (SARS_RUN_ID=tr1_.ID)
Total runtime: 1606.728 ms 1.6 seconds <- very good response time improvement
(7 rows)
Questions:
Any concerns with setting these conf variables you recommended; work_mem, random_page_cost dbserver wide (in postgresql,conf)?
Thanks so much!!!
-------- Original Message --------
Subject: Re: [GENERAL] Very slow inner join query Unacceptable
latency.
From: Scott Marlowe <scott.marlowe@xxxxxxxxx>
Date: Thu, May 23, 2013 11:16 pm
To: fburgess@xxxxxxxxxxxxxxx
Cc: Jaime Casanova <jaime@xxxxxxxxxxxxxxx>, psql performance list
<pgsql-performance@xxxxxxxxxxxxxx>, Postgres General
<pgsql-general@xxxxxxxxxxxxxx>
Looking at the execution plan makes me wonder what your work_mem is
set to. Try cranking it up to test and lowering random_page_cost:
set work_mem='500MB';
set random_page_cost=1.2;
explain analyze select ...
and see what you get.
