From: Amit Kapila <amit.kapila@huawei.com>
To: <fburgess@radiantblue.com>, "'Jeff Janes'" <jeff.janes@gmail.com>
CC: "'Jaime Casanova'" <jaime@2ndquadrant.com>, "'psql performance list'"
	<pgsql-performance@postgresql.org>, "'Postgres General'"
	<pgsql-general@postgresql.org>
References: 
 <20130523102128.5a830134ae84016b0174832fdc1a3173.e1ed00cc7b.wbe@email11.secureserver.net>
In-Reply-To: 
 <20130523102128.5a830134ae84016b0174832fdc1a3173.e1ed00cc7b.wbe@email11.secureserver.net>
Subject: Re: [PERFORM] Very slow inner join query Unacceptable latency.
Date: Fri, 24 May 2013 10:44:44 +0530
Message-ID: <005b01ce583d$9b4edfd0$d1ec9f70$@kapila@huawei.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: Ac5X2hxSBiGjNJ05R3SxS1kWj//n1AAYZIdw
Content-Language: en-us
Precedence: bulk
Sender: pgsql-general-owner@postgresql.org


On Thursday, May 23, 2013 10:51 PM fburgess wrote:
> serverdb=3D# set enable_hashjoin=3Doff;
> SET
> serverdb=3D# explain select count(*) as y0_ from SARS_ACTS this_ inner jo=
in SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=3Dtr1_.ID where tr1.ALGORITHM=3D=
'SMAT';

>                                                     QUERY PLAN
> -------------------------------------------------------------------------=
-------------------------------------------------
> Aggregate  (cost=3D7765563.69..7765563.70 rows=3D1 width=3D0)=20
>      Nested Loop  (cost=3D0.00..7765555.35 rows=3D3336 width=3D0)
>         -> Index Scan using idx_sars_acts_run_algorithm on sars_acts_run =
tr1_  (cost=3D0.00..44.32 rows=3D650 width=3D8)=20
>              Index Cond:  ((algorithm)::text =3D 'SMAT'::text)
>         -> Index Scan using idx_sars_acts_run_id_end_time on sars_acts th=
is_  (cost=3D0.00..11891.29 rows=3D4452 width=3D8)=20
>              Index Cond:  (SARS_RUN_ID=3Dtr1_.ID)
>(6 rows)

>serverdb=3D# \timing
>TIming is on.

>serverdb=3D# select count(*) as y0_ from SARS_ACTS this_ inner join SARS_A=
CTS_RUN tr1_ on this_.SARS_RUN_ID=3Dtr1_.ID where tr1.ALGORITHM=3D'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 is the reason why optimizer wo=
uld have selected=20
Hash Join.=20

serverdb=3D# explain analyze select count(*) as y0_ from SARS_ACTS this_ in=
ner join SARS_ACTS_RUN tr1_ on this_.SARS_RUN_ID=3Dtr1_.ID where tr1_.ALGOR=
ITHM=3D'SMAT';
                                                    QUERY PLAN
---------------------------------------------------------------------------=
-----------------------------------------------
Aggregate  (cost=3D3983424.05..3983424.06 rows=3D1 width=3D0) (actual time=
=3D1358298.003..1358298.004 rows=3D1 loops=3D1)
  -> Hash Join  (cost=3D44.93..3983415.81 rows=3D3297 width=3D0) (actual ti=
me=3D2593.768..1358041.205 rows 1481710 loops=3D1)


It is quite surprising that after optimizer decided the cost of some plan (=
Hash Join) to be lower but actual execution cost of same is more.=20
There might be some problem with cost calculation model of Hash Join for so=
me 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, because as the data changes in yo=
ur 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 sche=
ma and data for the tables/indexes used in query?

With Regards,
Amit Kapila.


--=20
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general