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

<span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>serverdb=# set enable_hashjoin=off;<br />SET<br
/>serverdb=#explain select count(*) as y0_ from SARS_ACTS this_ inner join <span
style="color:rgb(0,0,0);white-space:pre-wrap">SARS_ACTS_RUN</span>tr1_ on this_.SARS_RUN_ID=<a
href="http://tr1_.ID">tr1_.ID</a>where tr1.ALGORITHM='SMAT';<br /><br
/>                                                   QUERY PLAN<br
/>--------------------------------------------------------------------------------------------------------------------------<br
/>Aggregate (cost=7765563.69..7765563.70 rows=1 width=0) <br />      Nested Loop  (cost=0.00..7765555.35 rows=3336
width=0)<br/>         -> Index Scan using idx_sars_acts_run_algorithm on sars_acts_run tr1_  (cost=0.00..44.32
rows=650width=8) <br />              Index Cond:  ((algorithm)::text = 'SMAT'::text)<br />         -> Index Scan
usingidx_sars_acts_run_id_end_time on sars_acts this_  (cost=0.00..11891.29 rows=4452 width=8) <br />             
IndexCond:  (SARS_RUN_ID=<a href="http://tr1_.ID">tr1_.ID</a>)<br />(6 rows)<br /><br />serverdb=# \timing<br />TIming
ison.<br /><br />serverdb=# select count(*) as y0_ from SARS_ACTS this_ inner join <span
style="color:rgb(0,0,0);white-space:pre-wrap">SARS_ACTS_RUN</span>tr1_ on this_.SARS_RUN_ID=<a
href="http://tr1_.ID">tr1_.ID</a>where tr1.ALGORITHM='SMAT';<br /> y0_<br />------<br />1481710<br />(1 row)<br /><br
/>Time:85069.416 ms < 1.4 minutes <-- not great, but much better!<br /><br />Subsequently, runs in the
millisecondsonce cached.</div><div><br /></div><div>But what negative impact is disabling hash joins?</div><div><br
/></div><div>Sorry,I just executed the explain without the analyze, I'll send out the "explain analyze" next
reply.</div><div><br/></div><div>thanks</div><div><br /></div><div>Freddie<br /></div><div><br /></div><blockquote
id="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: Jeff Janes <<a
href="mailto:jeff.janes@gmail.com">jeff.janes@gmail.com</a>><br/> Date: Wed, May 22, 2013 5:17 pm<br /> To: <a
href="mailto:fburgess@radiantblue.com">fburgess@radiantblue.com</a><br/> Cc: Jaime Casanova <<a
href="mailto:jaime@2ndquadrant.com">jaime@2ndquadrant.com</a>>,psql performance list<br /> <<a
href="mailto:pgsql-performance@postgresql.org">pgsql-performance@postgresql.org</a>>,Postgres General<br /> <<a
href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>><br/><br /><div dir="ltr">On Wed, May
22,2013 at 7:41 AM, <span dir="ltr"><<a href="mailto:fburgess@radiantblue.com"
target="_blank">fburgess@radiantblue.com</a>></span>wrote:<br /><div class="gmail_extra"><div
class="gmail_quote"><blockquoteclass="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div><span
style="font-size:10pt;font-family:Verdana"><div>PostgreSQL 9.1.6 on linux<br /></div></span></div></blockquote><div><br
/></div><div><br/></div><div style="">From the numbers in your attached plan, it seems like it should be doing a nested
loopfrom the 580 rows (it thinks) that match in <span style="color:rgb(0,0,0);white-space:pre-wrap">SARS_ACTS_RUN
againstthe index on sars_run_id to pull out the </span><span style="color:rgb(0,0,0);white-space:pre-wrap">3297 rows
(again,it think, though it is way of there)</span><span style="color:rgb(0,0,0);white-space:pre-wrap">. I can't see why
itwould 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</span></div><div style=""><span
style="color:rgb(0,0,0);white-space:pre-wrap"><br/></span></div><div style=""><font color="#000000"><span
style="white-space:pre-wrap">Couldyou "set enable_hashjoin to off" and post the "explain analyze" that that
gives?</span></font><br/></div><div style=""><font color="#000000"><span style="white-space:pre-wrap"><br
/></span></font></div><divstyle=""><font color="#000000"><span style="white-space:pre-wrap"><br
/></span></font></div><divstyle=""><font color="#000000"><span
style="white-space:pre-wrap">Cheers,</span></font></div><divstyle=""><font color="#000000"><span
style="white-space:pre-wrap"><br/></span></font></div><div style=""><font color="#000000"><span
style="white-space:pre-wrap">Jeff</span></font></div><div> </div></div></div></div></div></blockquote></span>

On Thu, May 23, 2013 at 12:21 PM,  <fburgess@radiantblue.com> wrote:
>
> But what negative impact is disabling hash joins?
>

doing it just for a single query, could be a tool for solving
particular problems.
setting it in postgresql.conf, therefore affecting all queries, is
like using a hammer to change tv channel... it will cause more
problems than the one it solves.

what you can do is:

1) execute:

SET enable_hashjoin TO OFF;
SELECT here
RESET enable_hashjoin TO ON;

2) in a function:

CREATE FUNCTION do_something() RETURNS bigint AS
$$
   SELECT here
$$ LANGUAGE sql SET enable_hashjoin TO OFF STABLE;

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566         Cell: +593 987171157

On Thursday, May 23, 2013 10:51 PM fburgess wrote:
> 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_.IDwhere 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
wheretr1.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.

If I see the plan from your other mail as below where Hash join is selected, the cost of Nested Loop is much more, that
isthe reason why optimizer would have selected  
Hash Join.

serverdb=# explain analyze 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=3983424.05..3983424.06 rows=1 width=0) (actual time=1358298.003..1358298.004 rows=1 loops=1)
  -> Hash Join  (cost=44.93..3983415.81 rows=3297 width=0) (actual time=2593.768..1358041.205 rows 1481710 loops=1)


It is quite surprising that after optimizer decided the cost of some plan (Hash Join) to be lower but actual execution
costof same is more.  
There might be some problem with cost calculation model of Hash Join for some cases.

By the way which version of PostgreSQL you are using?

> But what negative impact is disabling hash joins?

I think using it as a temporary fix might be okay, but keeping such code in your application might be risky for you,
becauseas the data changes in your tables, it could be quite possible that 
in future Hash Join might be the best and cheapest way.

Can you try reproducing it with small data or else can you attach your schema and data for the tables/indexes used in
query?

With Regards,
Amit Kapila.