From: stefan@konink.de
To: pgsql-bugs@postgresql.org
Subject: BUG #8130: Hashjoin still gives issues
Date: Wed, 01 May 2013 12:48:07 +0000
Message-ID: <E1UXWS7-0007V8-N2@wrigleys.postgresql.org> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-bugs>
The following bug has been logged on the website:
Bug reference: 8130
Logged by: Stefan de Konink
Email address: stefan@konink.de
PostgreSQL version: 9.2.4
Operating system: Linux
Description:
We figured out that two very close query give a massive difference
performance between using select * vs select id.
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;
With some help on IRC we figured out that "there was a bugfix in hash
estimation recently and I was hoping you were older than that", but since we
are not:
PostgreSQL 9.2.4 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc
(Gentoo 4.7.2-r1 p1.6, pie-0.5.5) 4.7.2, 64-bit
...there might still be a bug around.
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:
http://explain.depesz.com/s/AWB
What can we do to provide a bit more of information?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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: stefan@konink.de, pgsql-bugs@postgresql.org
Subject: Re: BUG #8130: Hashjoin still gives issues
In-Reply-To: <E1UXWS7-0007V8-N2@wrigleys.postgresql.org>
* 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