Message-ID: <45C29775.3020507@stccmop.org>
Date: Thu, 01 Feb 2007 17:44:21 -0800
From: Bill Howe <howew@stccmop.org>
User-Agent: Thunderbird 1.5.0.9 (X11/20061219)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: index scan through a subquery
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit


Why should these queries have different plans?


create table foo (a int PRIMARY KEY);

Q1: explain select max(a) from foo

> Result  (cost=0.04..0.05 rows=1 width=0)
>  InitPlan
>    ->  Limit  (cost=0.00..0.04 rows=1 width=4)
>          ->  Index Scan Backward using foo_pkey on foo
>  (cost=0.00..76.10 rows=2140 width=4)
>                Filter: (a IS NOT NULL)

Q2: explain select max(a) from (select * from foo) as f

> Aggregate  (cost=36.75..36.76 rows=1 width=4)
>  ->  Seq Scan on foo  (cost=0.00..31.40 rows=2140 width=4)


I need the lovely index scan, but my table is hidden behind a view, and
all I get is the ugly sequential scan.  Any ideas on how to convince the
optimizer to unfold the subquery properly?

Bill