one-field index vs. multi-field index planner estimates

Поиск
Список
Период
Сортировка
От Evgeny Gridasov
Тема one-field index vs. multi-field index planner estimates
Дата
Msg-id 20060310194545.be002083.eugrid@fpm.kubsu.ru
обсуждение исходный текст
Ответ на Re: Hanging queries on dual CPU windows  (Jan de Visser <jdevisser@digitalfairway.com>)
Ответы Re: one-field index vs. multi-field index planner estimates  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Hello.

Recently I've discovered an interesting thing (Postgres version 8.1.3):

example table:

CREATE TABLE test (
 id INT,
 name TEXT,
 comment TEXT,
 phone TEXT,
 visible BOOLEAN
);

then,
CREATE INDEX i1 ON test(phone);
CREATE INDEX i2 ON test(phone, visible);
CREATE INDEX i3 ON test(phone, visible) WHERE visible;

then insert lot's of data
and try to execute query like:

SELECT * FROM test WHERE phone='12345' AND visible;

uses index i1, and filters all visible fields.
When I drop index i1, postgres starts to use index i2
and the query began to work much more faster.

When I drop index i2, postgres uses index i3 which is faster than i2 ofcourse.

I've noticed that planner estimated all queries for all three cases with the same cost.
So, is it a planner bad estimate or what?

--
Evgeny Gridasov
Software Engineer
I-Free, Russia

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

Предыдущее
От: Jan de Visser
Дата:
Сообщение: Re: Hanging queries on dual CPU windows
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: Process Time X200