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!!!