Re: [PERFORM] Very slow inner join query Unacceptable latency.

<span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>PostgreSQL 9.1.6 on linux<br /><br
/></div><blockquoteid="replyBlockquote" style="border-left: 2px solid blue; margin-left: 8px; padding-left: 8px;
font-size:10pt;color:black; font-family:verdana;" webmail="1"><div id="wmQuoteWrapper"> -------- Original Message
--------<br/> Subject: Re: [PERFORM] 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:59 pm<br /> To:
FreddieBurgess <<a href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a>><br /> Cc: psql
performancelist <<a href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>>,
Postgres<br/> General <<a href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>><br /><br
/>On Tue, May 21, 2013 at 4:53 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_idON SARS_ACTS USING btree<br /> > (sars_run_id)<br /> > we have pk constraint on the
SARS_ACTS_RUNtable; 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 /> can you please show us an EXPLAIN ANALYZE of
thisquery (not only<br /> EXPLAIN). please paste it in a file and attach it so it doesn't get<br /> reformatted by the
mailclient.<br /><br /> what version of postgres is this?<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 /></div></blockquote></span>