serverdb=# set enable_hashjoin=off;
SET
serverdb=# explain 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=7765563.69..7765563.70 rows=1 width=0)
Nested Loop (cost=0.00..7765555.35 rows=3336 width=0)
-> Index Scan using idx_sars_acts_run_algorithm on sars_acts_run tr1_ (cost=0.00..44.32 rows=650 width=8)
Index Cond: ((algorithm)::text = 'SMAT'::text)
-> Index Scan using idx_sars_acts_run_id_end_time on sars_acts this_ (cost=0.00..11891.29 rows=4452 width=8)
Index Cond: (SARS_RUN_ID=
tr1_.ID)
(6 rows)
serverdb=# \timing
TIming is on.
serverdb=# 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';
y0_
------
1481710
(1 row)
Time: 85069.416 ms < 1.4 minutes <-- not great, but much better!
Subsequently, runs in the milliseconds once cached.
Sorry, I just executed the explain without the analyze, I'll send out the "explain analyze" next reply.