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

From: Evgeny Gridasov <eugrid(at)fpm(dot)kubsu(dot)ru>
To: pgsql-performance(at)postgresql(dot)org
Subject: one-field index vs. multi-field index planner estimates
Date: 2006-03-10 16:45:45
Message-ID: 20060310194545.be002083.eugrid@fpm.kubsu.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2006-03-10 16:46:56 Re: Process Time X200
Previous Message Jan de Visser 2006-03-10 16:13:35 Re: Hanging queries on dual CPU windows