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
Re: The planner chooses seqscan+sort when there is an index on the sort column |
| Список | 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 по дате отправления: