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 <t...@sss.pgh.pa.us> wrote: > Matt Daw <m...@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 >
