Very slow inner join query unacceptable latency
<html><body><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 constraint on 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> QUERY PLAN<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>&nbsp;&nbsp;&nbsp; -> Seq Scan om sars_acts this_ (cost=0.00..3844241.84 rows=37092284 width=8)<br> -> Hash (cost=230565.81..230565.81 rows=580 width=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 approximately 5.3 minutes to complete, very very slow, our users are not happy.</div><div><br></div><div> Does anyone have suggestions about how to speed it up?<br><br>thanks <br mce_bogus="1"></div></span></body></html>
On Tue, May 21, 2013 at 3:54 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';
This is not a bug, you should write to the
pgsql-general@postgresql.org or pgsql-performance@postgresql.org
mailing lists.
anyway, seems that you need an additional index on SARS_ACTS_RUN.ALGORITHM
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>Thanks Jaime for your feedback, 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 <span style="font-family:Verdana; color:#000000; font-size:10pt;">sars_acts_run</span> table, but the overall run time remained the same. It seems like the bottleneck is in the Seq Scan on the sars_acts table.<br></div><div><br></div><div><span style="font-family:Verdana; color:#000000; font-size:10pt;"> -> Seq Scan on sars_acts_run tr1_ (cost=0.00..230565.81 rows=580 width=8)<br> Filter: ((algorithm)::text = 'SMAT'::text)</span></div><div><span style="font-family:Verdana; color:#000000; font-size:10pt;"><br></span></div><div><span style="font-family:Verdana; color:#000000; font-size:10pt;">I'll move this posting into the appropriate group</span></div><div><span style="font-family:Verdana; color:#000000; font-size:10pt;"><br></span></div><div><span style="font-family:Verdana; color:#000000; font-size:10pt;">thanks</span></div><div><span style="font-family:Verdana; color:#000000; font-size:10pt;"><br></span></div>
<blockquote id="replyBlockquote" webmail="1" style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px; font-size:10pt; color:black; font-family:verdana;">
<div id="wmQuoteWrapper">
-------- Original Message --------<br>
Subject: Re: [BUGS] Very slow inner join query unacceptable latency<br>
From: Jaime Casanova <<a href="mailto:jaime@2ndquadrant.com">jaime@2ndquadrant.com</a>><br>
Date: Tue, May 21, 2013 2:34 pm<br>
To: Freddie Burgess <<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>><br>
Cc: pgsql-bugs <<a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a>><br>
<br>
On Tue, May 21, 2013 at 3:54 PM, <<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>> wrote:<br>
> 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<br>
> (sars_run_id)<br>
> we have pk constraint on the SARS_ACTS_RUN table; sars_acts_run_pkey PRIMARY<br>
> KEY (id )<br>
><br>
> serverdb=# explain select count(*) as y0_ from SARS_ACTS this_ inner join<br>
> SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a> where tr1_.ALGORITHM='SMAT';<br>
<br>
This is not a bug, you should write to the<br>
<a href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a> or <a href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a><br>
mailing lists.<br>
<br>
anyway, seems that you need an additional index on SARS_ACTS_RUN.ALGORITHM<br>
<br>
--<br>
Jaime Casanova <a href="http://www.2ndQuadrant.com">www.2ndQuadrant.com</a><br>
Professional PostgreSQL: Soporte 24x7 y capacitación<br>
Phone: +593 4 5107566 Cell: +593 987171157<br>
<br>
<br>
-- <br>
Sent via pgsql-bugs mailing list (<a href="mailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href="http://www.postgresql.org/mailpref/pgsql-bugs">http://www.postgresql.org/mailpref/pgsql-bugs</a><br>
</div>
</blockquote></span></body></html>
Import Notes
Resolved by subject fallback