Thread: Very slow inner join query Unacceptable latency.
<span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>The SARS_ACTS table currently has 37,115,515 rows<br/><br />we have indexed: idx_sars_acts_acts_run_id ON SARS_ACTS USING btree (sars_run_id)<br />we have pk constrainton the SARS_ACTS_RUN table; sars_acts_run_pkey PRIMARY KEY (id )<br /><br />serverdb=# explain select count(*)as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a>where tr1_.ALGORITHM='SMAT';<br /> QUERYPLAN<br />--------------------------------------------------------------------------------------------------------------------------<br />Aggregate (cost=4213952.17..4213952.18 rows=1 width=0)<br /> -> Hash Join (cost=230573.06..4213943.93 rows=3296 width=0)<br/> Hash Cond: (this_.SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a>)<br /> -> Seq Scan om sars_actsthis_ (cost=0.00..3844241.84 rows=37092284 width=8)<br /> -> Hash (cost=230565.81..230565.81 rows=580width=8)<br /> -> Seq Scan on sars_acts_run tr1_ (cost=0.00..230565.81 rows=580 width=8)<br /> Filter: ((algorithm)::text = 'SMAT'::text)<br />(7 rows)<br /><br />This query executes in approximately5.3 minutes to complete, very very slow, our users are not happy.<br /><br />I did add an index on SARS_ACTS_RUN.ALGORITHMcolumn but it didn't improve the run time. <br />The planner just changed the "Filter:" to an "IndexScan:" improving the cost of the Seq Scan <br />on the sars_acts_run table, but the overall run time remained the same.It seems like the bottleneck <br />is in the Seq Scan on the sars_acts table.<br /><br /> -> Seq Scanon sars_acts_run tr1_ (cost=0.00..230565.81 rows=580 width=8)<br /> Filter: ((algorithm)::text ='SMAT'::text)<br /><br />Does anyone have suggestions about how to speed it up?<br /><a href="mailto:pgsql-performance@postgresql.org"target="_blank"></a></div></span>
On Tue, May 21, 2013 at 4:53 PM, <fburgess@radiantblue.com> wrote: > The SARS_ACTS table currently has 37,115,515 rows > > we have indexed: idx_sars_acts_acts_run_id ON SARS_ACTS USING btree > (sars_run_id) > we have pk constraint on the SARS_ACTS_RUN table; sars_acts_run_pkey PRIMARY > KEY (id ) > > 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'; can you please show us an EXPLAIN ANALYZE of this query (not only EXPLAIN). please paste it in a file and attach it so it doesn't get reformatted by the mail client. what version of postgres is this? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157
On Wednesday, May 22, 2013 3:24 AM fburgess wrote: > The SARS_ACTS table currently has 37,115,515 rows > we have indexed: idx_sars_acts_acts_run_id ON SARS_ACTS USING btree (sars_run_id) > we have pk constraint on the SARS_ACTS_RUN table; sars_acts_run_pkey PRIMARY KEY (id ) > serverdb=# explain select count(*) as y0_ from SARS_ACTS this_ inner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=tr1_.IDwhere tr1_.ALGORITHM='SMAT'; > QUERY PLAN > -------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=4213952.17..4213952.18 rows=1 width=0) > -> Hash Join (cost=230573.06..4213943.93 rows=3296 width=0) > Hash Cond: (this_.SARS_RUN_ID=tr1_.ID) > -> Seq Scan om sars_acts this_ (cost=0.00..3844241.84 rows=37092284 width=8) > -> Hash (cost=230565.81..230565.81 rows=580 width=8) > -> Seq Scan on sars_acts_run tr1_ (cost=0.00..230565.81 rows=580 width=8) > Filter: ((algorithm)::text = 'SMAT'::text) > (7 rows) > This query executes in approximately 5.3 minutes to complete, very very slow, our users are not happy. > I did add an index on SARS_ACTS_RUN.ALGORITHM column but it didn't improve the run time. > The planner just changed the "Filter:" to an "Index Scan:" improving the cost of the Seq Scan > on the sars_acts_run table, but the overall run time remained the same. It seems like the bottleneck > is in the Seq Scan on the sars_acts table. > -> Seq Scan on sars_acts_run tr1_ (cost=0.00..230565.81 rows=580 width=8) > Filter: ((algorithm)::text = 'SMAT'::text) > Does anyone have suggestions about how to speed it up? Could you please once trying Analyzing both tables and then run the query to check which plan it uses: Analyze SARS_ACTS; Analyze SARS_ACTS_RUN; With Regards, Amit Kapila.