index scan through a subquery

Поиск
Список
Период
Сортировка
От Bill Howe
Тема index scan through a subquery
Дата
Msg-id 45C29775.3020507@stccmop.org
обсуждение исходный текст
Ответы Re: index scan through a subquery
Список 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

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Mischa Sandberg"
Дата:
Сообщение: Re: Tuning
Следующее
От: "Karen Hill"
Дата:
Сообщение: How long should it take to insert 200,000 records?