BUG #8130: Hashjoin still gives issues

From: stefan(at)konink(dot)de
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #8130: Hashjoin still gives issues
Date: 2013-05-01 12:48:07
Message-ID: E1UXWS7-0007V8-N2@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-performance

The following bug has been logged on the website:

Bug reference: 8130
Logged by: Stefan de Konink
Email address: stefan(at)konink(dot)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/jRx
http://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?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David Fetter 2013-05-01 14:26:53 Re: [BUGS] BUG #8128: pg_dump (>= 9.1) failed while dumping a scheme named "old" from PostgreSQL 8.4
Previous Message Adrian.Vondendriesch 2013-05-01 11:59:02 Re: BUG #8128: pg_dump (>= 9.1) failed while dumping a scheme named "old" from PostgreSQL 8.4

Browse pgsql-performance by date

  From Date Subject
Next Message Anne Rosset 2013-05-01 14:05:06 Deterioration in performance when query executed in multi threads
Previous Message Mark Kirkwood 2013-05-01 09:43:36 Re: In progress INSERT wrecks plans on table