From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jay Levitt <jay.levitt@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Subquery in a JOIN not getting restricted?
Date: Mon, 07 Nov 2011 16:41:18 -0500
Message-ID: <3429.1320702078@sss.pgh.pa.us> (raw)
In-Reply-To: <4EB84CB4.2010102@gmail.com>
References: <4EB84CB4.2010102@gmail.com>
Jay Levitt <jay.levitt@gmail.com> writes:
> When I run the following query:
> select questions.id
> from questions
> join (
> select u.id as user_id
> from users as u
> left join scores as s
> on s.user_id = u.id
> ) as subquery
> on subquery.user_id = questions.user_id;
> the subquery is scanning my entire user table, even though it's restricted
> by the outer query. (My real subquery is much more complicated, of course,
> but this is the minimal fail case.)
> Is this just not a thing the optimizer can do?
Every release since 8.2 has been able to reorder joins in a query
written that way. Probably it just thinks it's cheaper than the
alternatives.
(Unless you've reduced the collapse_limit variables for some reason?)
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, jay.levitt@gmail.com
Subject: Re: Subquery in a JOIN not getting restricted?
In-Reply-To: <3429.1320702078@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