From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Hills <mark@pogo.org.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index condition in a Nested Loop
Date: Sun, 26 Feb 2012 16:00:20 -0500
Message-ID: <16098.1330290020@sss.pgh.pa.us> (raw)
In-Reply-To: <alpine.LNX.2.01.1202261358070.31287@stax.localdomain>
References: <alpine.LNX.2.01.1202261358070.31287@stax.localdomain>
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
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, mark@pogo.org.uk
Subject: Re: Index condition in a Nested Loop
In-Reply-To: <16098.1330290020@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