The planner chooses seqscan+sort when there is an index on the sort column

Поиск
Список
Период
Сортировка
От Csaba Nagy
Тема The planner chooses seqscan+sort when there is an index on the sort column
Дата
Msg-id 1146670652.14093.171.camel@coppola.muc.ecircle.de
обсуждение исходный текст
Ответы Re: The planner chooses seqscan+sort when there is an index on the sort column  ("John D. Burger" <john@mitre.org>)
Re: The planner chooses seqscan+sort when there is an index on the sort column  (Andreas Kretschmer <akretschmer@spamfence.net>)
Список pgsql-general
Hi all,

I wonder why this happens:

 - postgres: 8.1.3
 - the table has ~200 million rows;
 - there is a primary key on (col_1, col_2);
 - the table was ANALYZEd;
 - the planner chooses seqscan+sort for the following query even with
enable_seqscan=off:

select * from table order by col_1;

Isn't it supposed to choose the index scan at least when
enable_seqscan=off ? Even if it is indeed not faster to do the index
scan than seqscan+sort.

The actual plan looks like (names changed):

db=# set enable_seqscan = off;
SET
db=# explain select * from table order by col_1;
                                  QUERY PLAN
-----------------------------------------------------------------------------------------
 Sort  (cost=165585198.70..166102386.06 rows=206874944 width=60)
   Sort Key: col_1
   ->  Seq Scan on table  (cost=100000000.00..104552091.44
rows=206874944 width=60)
(3 rows)


db=# \d table
                                     Table "public.table"
     Column      |            Type             |
Modifiers
-----------------+-----------------------------+----------------------------------------------------
 col_1           | bigint                      | not null
 col_2           | bigint                      | not null
...
Indexes:
    "pk_table" PRIMARY KEY, btree (col_1, col_2)
...


Cheers,
Csaba.



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

Предыдущее
От: "Javier de la Torre"
Дата:
Сообщение: Re: psql is slow and it does not take much resources
Следующее
От: "John D. Burger"
Дата:
Сообщение: Re: The planner chooses seqscan+sort when there is an index on the sort column