From: Amit Kapila <amit.kapila@huawei.com>
To: fburgess@radiantblue.com
To: 'Jeff Janes' <jeff.janes@gmail.com>
Cc: 'Jaime Casanova' <jaime@2ndquadrant.com>
Cc: 'psql performance list' <pgsql-performance@postgresql.org>
Cc: 'Postgres General' <pgsql-general@postgresql.org>
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> (raw)
In-Reply-To: <20130523102128.5a830134ae84016b0174832fdc1a3173.e1ed00cc7b.wbe@email11.secureserver.net>
References: <20130523102128.5a830134ae84016b0174832fdc1a3173.e1ed00cc7b.wbe@email11.secureserver.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
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_.ID where 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 where tr1.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 is the 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_.ID where 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 cost of 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, because as 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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: amit.kapila@huawei.com, fburgess@radiantblue.com, jeff.janes@gmail.com, jaime@2ndquadrant.com, pgsql-general@postgresql.org
Subject: Re: [PERFORM] Very slow inner join query Unacceptable latency.
In-Reply-To: <005b01ce583d$9b4edfd0$d1ec9f70$@kapila@huawei.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox