pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Mikkel Lauritsen <renard@tala.dk>
To: pgsql-performance@postgresql.org
Subject: Different execution plans for semantically equivalent queries
Date: Fri, 04 Feb 2011 16:22:02 +0100
Message-ID: <bbe20a8f4ef774a3f221f637b1d1cac1@localhost> (raw)

Hi all,

I have a execution planner related issue that I'd like to have some help
in understanding a bit deeper -

I have a table which basically contains fields for a value, a timestamp
and
a record type which is an integer. I would like to do a query which
retrieves
the newest record for each type, and the persistence framework that I'm
using
does something which is structurally like

SELECT * FROM table t1 WHERE 0 = (SELECT COUNT(*) FROM table t2 WHERE
    t2.type = t1.type AND t2.timestamp > t1.timestamp)

On all of the PostgreSQL versions I've tried (9.0.2 included) this
executes
in about 20-30 seconds, which isn't exactly stellar. I've tried the (I
think)
equivalent

SELECT * FROM table t1 WHERE NOT EXISTS (SELECT * FROM table t2 WHERE
    t2.type = t1.type AND t2.timestamp > t1.timestamp)

instead, and that executes in about 100 ms, so it's about 200 times
faster.

The two statements have completely different execution plans, so I
understand
why there is a difference in performance, but as I'm unable to modify the
SQL that the persistence framework generates I'd like to know if there's
anything that I can do in order to make the first query execute as fast as
the second one.

I'm more specifically thinking whether I'm missing out on a crucial
planner
configuration knob or something like that, which causes the planner to
treat the two cases differently.

Best regards & thanks for an excellent database engine,
  Mikkel Lauritsen

view thread (4+ messages)  latest in thread

Message-ID: <bbe20a8f4ef774a3f221f637b1d1cac1@localhost>
Permalink:  ../bbe20a8f4ef774a3f221f637b1d1cac1@localhost/
Also on:    postgresql.org/message-id/bbe20a8f4ef774a3f221f637b1d1cac1@localhost

copy link · copy postgr.es

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: renard@tala.dk
  Subject: Re: Different execution plans for semantically equivalent queries
  In-Reply-To: <bbe20a8f4ef774a3f221f637b1d1cac1@localhost>

* 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