Re: Query plan, nested EXISTS

Hi Tom, thank you very much. I'll load these tables onto a 9.2 instance and
report back.

Matt

On Fri, Sep 28, 2012 at 2:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Matt Daw <matt(at)shotgunsoftware(dot)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
>