From: Stefan de Konink <stefan@konink.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: [BUGS] BUG #8130: Hashjoin still gives issues
Date: Wed, 1 May 2013 17:44:54 +0200 (CEST)
Message-ID: <alpine.LNX.2.00.1305011724480.23816@kinkrsoftware.nl> (raw)
In-Reply-To: <3469.1367421052@sss.pgh.pa.us>
References: <E1UXWS7-0007V8-N2@wrigleys.postgresql.org>
<3469.1367421052@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Dear Tom,
On Wed, 1 May 2013, Tom Lane wrote:
>> What can we do to provide a bit more of information?
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> There is no particularly good reason to think this is a bug; please
> take it up on pgsql-performance if you have more questions.
I beg to disagree, the performance of a select * query and the select b.id
query are both "hot". The result in a fundamentally different query plan
(and performance). Combined with the recent bugfix regarding hash
estimation, it gives me a good indication that there might be a bug.
I am not deep into the query optimiser of PostgreSQL but given the above
same were different selections can change an entire query plan (and * is
in fact out of the box 30 times faster than b.id) it does. When hash is
disabled the entire query is -depending on the system checked- 2 to
30x faster.
The original query:
select * from ambit_privateevent_calendars as a, ambit_privateevent as b,
ambit_calendarsubscription as c, ambit_calendar as d where c.calendar_id =
d.id and a.privateevent_id = b.id and c.user_id = 1270 and c.calendar_id
= a.calendar_id and c.STATUS IN (1, 8, 2, 15, 18, 4, 12, 20) and not
b.main_recurrence = true;
select b.id from ambit_privateevent_calendars as a, ambit_privateevent as
b, ambit_calendarsubscription as c, ambit_calendar as d where c.calendar_id =
d.id and a.privateevent_id = b.id and c.user_id = 1270 and c.calendar_id
= a.calendar_id and c.STATUS IN (1, 8, 2, 15, 18, 4, 12, 20) and not
b.main_recurrence = true;
(select * => select b.id, the star query is *fastest*)
We compare:
http://explain.depesz.com/s/jRxhttp://explain.depesz.com/s/eKE
By setting "set enable_hashjoin = off;" performance in our entire
application increased 30 fold in throughput, which was a bit unexpected
but highly appreciated. The result of the last query switch the mergejoin:
http://explain.depesz.com/s/AWB
It is also visible that after hashjoin is off, the b.id query is faster
than the * query (what would be expected).
Our test machine is overbudgetted, 4x the memory of the entire database
~4GB, and uses the PostgreSQL stock settings.
Stefan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: stefan@konink.de, tgl@sss.pgh.pa.us
Subject: Re: [BUGS] BUG #8130: Hashjoin still gives issues
In-Reply-To: <alpine.LNX.2.00.1305011724480.23816@kinkrsoftware.nl>
References: <E1UXWS7-0007V8-N2@wrigleys.postgresql.org> <3469.1367421052@sss.pgh.pa.us> <alpine.LNX.2.00.1305011724480.23816@kinkrsoftware.nl>
* 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