To: Mark Hills <mark@pogo.org.uk>
cc: pgsql-performance@postgresql.org
Subject: Re: Index condition in a Nested Loop 
In-reply-to: <alpine.LNX.2.01.1202261358070.31287@stax.localdomain> 
References: <alpine.LNX.2.01.1202261358070.31287@stax.localdomain>
Comments: In-reply-to Mark Hills <mark@pogo.org.uk>
	message dated "Sun, 26 Feb 2012 14:16:47 +0000"
Date: Sun, 26 Feb 2012 16:00:20 -0500
Message-ID: <16098.1330290020@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Mark Hills <mark@pogo.org.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