Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Date:
<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>

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

From
Jeff Janes
Date:
On Wed, May 22, 2013 at 7:41 AM, <fburgess@radiantblue.com> wrote:
PostgreSQL 9.1.6 on linux


From the numbers in your attached plan, it seems like it should be doing a nested loop from the 580 rows (it thinks) that match in SARS_ACTS_RUN against the index on sars_run_id to pull out the 3297 rows (again, it think, though it is way of there). I can't see why it would not do that. There were some planner issues in the early 9.2 releases that caused very large indexes to be punished, but I don't think those were in 9.1

Could you "set enable_hashjoin to off" and post the "explain analyze" that that gives?


Cheers,

Jeff
 

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

From
Scott Marlowe
Date:
Looking at the execution plan makes me wonder what your work_mem is
set to.  Try cranking it up to test and lowering random_page_cost:

set work_mem='500MB';
set random_page_cost=1.2;
explain analyze select ...

and see what you get.