Message-ID: <4EB88C5A.2020704@gmail.com>
Date: Mon, 07 Nov 2011 20:56:42 -0500
From: Jay Levitt <jay.levitt@gmail.com>
User-Agent: Postbox 3.0.1 (Macintosh/20111102)
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: pgsql-performance@postgresql.org
Subject: Re: Subquery in a JOIN not getting restricted?
References: <4EB84CB4.2010102@gmail.com>
	<4EB7FEFD0200002500042B1C@gw.wicourts.gov>
	<4EB80CAA0200002500042B3B@gw.wicourts.gov>
	<4EB86784.7080209@gmail.com>
In-Reply-To: <4EB86784.7080209@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jay Levitt wrote:
> And yep! When I do a CREATE TABLE AS from that view, and add an index on
> user_id, it works just as I'd like.

Or not.  Feel free to kick me back over to pgsql-novice, but I don't get why 
the GROUP BY in this subquery forces it to scan the entire users table (seq 
scan here, index scan on a larger table) when there's only one row in users 
that can match:

create table questions (
   id int not null primary key,
   user_id int not null
);
insert into questions
   select generate_series(1,1100), (random()*2000)::int;

create table users (
   id int not null primary key
);
insert into users select generate_series(1, 2000);

vacuum freeze analyze;

explain analyze
select questions.id
from questions
join (
   select u.id
   from users as u
   group by u.id
) as s
on s.id = questions.user_id
where questions.id = 1;


Hash Join  (cost=42.28..89.80 rows=2 width=4) (actual time=0.857..1.208 
rows=1 loops=1)
    Hash Cond: (u.id = questions.user_id)
    ->  HashAggregate  (cost=34.00..54.00 rows=2000 width=4) (actual 
time=0.763..1.005 rows=2000 loops=1)
          ->  Seq Scan on users u  (cost=0.00..29.00 rows=2000 width=4) 
(actual time=0.003..0.160 rows=2000 loops=1)
    ->  Hash  (cost=8.27..8.27 rows=1 width=8) (actual time=0.015..0.015 
rows=1 loops=1)
          Buckets: 1024  Batches: 1  Memory Usage: 1kB
          ->  Index Scan using questions_pkey on questions  (cost=0.00..8.27 
rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=1)
                Index Cond: (id = 1)
  Total runtime: 1.262 ms

This is on patched 9.0.5 built earlier today.  The real query has 
aggregates, so it really does need GROUP BY.. I think..

Jay