Re: Index condition in a Nested Loop

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mark Hills <mark(at)pogo(dot)org(dot)uk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Index condition in a Nested Loop
Date: 2012-02-26 21:00:20
Message-ID: 16098.1330290020@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Mark Hills <mark(at)pogo(dot)org(dot)uk> writes:
> What is that prevents the index condition from being used in earlier parts
> of the query? Only where a single condition is present is it be used below
> the final join.

"WHERE job.jid IN (1234)" is simplified to "WHERE job.jid = 1234", and
that in combination with "JOIN ON job.jid = middle.jid" allows deduction
of "middle.jid = 1234" a/k/a "task.jid = 1234", leading to the
recognition that only one row from "task" is needed. There is no such
transitive propagation of general IN clauses. The problem with your
slower queries is not that they're using merge joins, it's that there's
no scan-level restriction on the task table so that whole table has to
be scanned.

Another thing that's biting you is that the GROUP BY in the view acts as
a partial optimization fence: there's only a limited amount of stuff
that can get pushed down through that. You might consider rewriting the
view to avoid that, along the lines of

create view middle2 as
SELECT task.jid, task.tid,
(select count(resource.name) from resource where task.tid = resource.tid) AS nresource
FROM task;

This is not perfect: this formulation forces the system into essentially
a nestloop join between task and resource. In cases where you actually
want results for a lot of task rows, that's going to lose badly. But in
the examples you're showing here, it's going to work better.

regards, tom lane

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jeff Janes 2012-02-26 22:34:35 Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
Previous Message Stefan Keller 2012-02-26 20:56:35 Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?