From: Tom Lane <tgl@sss.pgh.pa.us>
To: Matt Daw <matt@shotgunsoftware.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query plan, nested EXISTS
Date: Fri, 28 Sep 2012 17:44:32 -0400
Message-ID: <16018.1348868672@sss.pgh.pa.us> (raw)
In-Reply-To: <CAA2LLOFzQDqnpvxxf1EAUzKXrJx5VUN3P2jgon3zTNzdtpGZQQ@mail.gmail.com>
References: <CAA2LLOFzQDqnpvxxf1EAUzKXrJx5VUN3P2jgon3zTNzdtpGZQQ@mail.gmail.com>
Matt Daw <matt@shotgunsoftware.com> writes:
> Howdy, I've been debugging a client's slow query today and I'm curious
> about the query plan. It's picking a plan that hashes lots of rows from the
> versions table (on v9.0.10)...
> EXPLAIN ANALYZE
> SELECT COUNT(*) FROM notes a WHERE
> a.project_id = 114 AND
> EXISTS (
> SELECT 1 FROM note_links b
> WHERE
> b.note_id = a.id AND
> b.entity_type = 'Version' AND
> EXISTS (
> SELECT 1 FROM versions c
> WHERE
> c.id = b.entity_id AND
> c.code ILIKE '%comp%' AND
> c.retirement_date IS NULL
> ) AND
> b.retirement_date IS NULL
> )
I think the real problem here is that 9.0 is incapable of avoiding a
full table scan on "note_links", which means it doesn't really have any
better option than to do the inner EXISTS as a full-table semijoin.
This is because it can't push a.id down through two levels of join, and
because the semijoins don't commute, there's no way to get a.id into the
scan of note_links to pull out only the useful rows. The hack with
LIMIT avoids this problem by preventing the inner EXISTS from being
treated as a full-fledged semijoin; but of course that hack leaves you
vulnerable to very bad plans if the statistics are such that a nestloop
join isn't the best bet for the inner EXISTS.
The work I did for parameterized paths in 9.2 was intended to address
exactly this type of scenario. I would be interested to know if 9.2
does this any better for you.
regards, tom lane
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: tgl@sss.pgh.pa.us, matt@shotgunsoftware.com
Subject: Re: Query plan, nested EXISTS
In-Reply-To: <16018.1348868672@sss.pgh.pa.us>
* 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