pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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/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 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



view thread (5+ messages)  latest in thread

Message-ID: <alpine.LNX.2.00.1305011724480.23816@kinkrsoftware.nl>
Permalink:  ../alpine.LNX.2.00.1305011724480.23816@kinkrsoftware.nl/
Also on:    postgresql.org/message-id/alpine.LNX.2.00.1305011724480.23816@kinkrsoftware.nl

 · 

reply

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