index scan through a subquery

From: Bill Howe <howew(at)stccmop(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: index scan through a subquery
Date: 2007-02-02 01:44:21
Message-ID: 45C29775.3020507@stccmop.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2007-02-02 09:51:42 Re: Subselect query enhancement
Previous Message Ben 2007-02-02 00:41:35 drive configuration for a new server