pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedone-field index vs. multi-field index planner estimates
4+ messages / 2 participants
[nested] [flat]
* one-field index vs. multi-field index planner estimates
@ 2006-03-10 16:45 Evgeny Gridasov <eugrid@fpm.kubsu.ru>
0 siblings, 1 reply; 4+ messages in thread
From: Evgeny Gridasov @ 2006-03-10 16:45 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: one-field index vs. multi-field index planner estimates
@ 2006-03-10 17:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Evgeny Gridasov <eugrid@fpm.kubsu.ru>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2006-03-10 17:09 UTC (permalink / raw)
To: Evgeny Gridasov <eugrid@fpm.kubsu.ru>; +Cc: pgsql-performance
Evgeny Gridasov <eugrid@fpm.kubsu.ru> writes:
> Recently I've discovered an interesting thing (Postgres version 8.1.3):
Have you ANALYZEd the table since loading it? What fraction of the rows
have visible = true?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: one-field index vs. multi-field index planner
@ 2006-03-10 17:28 Evgeny Gridasov <eugrid@fpm.kubsu.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Evgeny Gridasov @ 2006-03-10 17:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom,
ofcourse I've analyzed it.
visible is true for about 0.3% of all rows.
testing table contains about 300,000-500,000 rows.
On Fri, 10 Mar 2006 12:09:19 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Evgeny Gridasov <eugrid@fpm.kubsu.ru> writes:
> > Recently I've discovered an interesting thing (Postgres version 8.1.3):
>
> Have you ANALYZEd the table since loading it? What fraction of the rows
> have visible = true?
--
Evgeny Gridasov
Software Engineer
I-Free, Russia
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: one-field index vs. multi-field index planner
@ 2006-03-10 18:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Evgeny Gridasov <eugrid@fpm.kubsu.ru>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-03-10 18:58 UTC (permalink / raw)
To: Evgeny Gridasov <eugrid@fpm.kubsu.ru>; +Cc: pgsql-performance
Evgeny Gridasov <eugrid@fpm.kubsu.ru> writes:
> ofcourse I've analyzed it.
> visible is true for about 0.3% of all rows.
Well, I get an indexscan on i3 ... there isn't going to be any
strong reason for the planner to prefer i2 over i1, given that
the phone column is probably near-unique and the i2 index will be
bigger than i1. I don't see why it wouldn't like i3 though. Could
we see the EXPLAIN ANALYZE results with and without i3?
regression=# CREATE TABLE test (phone TEXT, visible BOOLEAN);
CREATE TABLE
regression=# insert into test select (z/2)::text,(z%1000)<=3 from generate_series(1,300000) z;
INSERT 0 300000
regression=# CREATE INDEX i1 ON test(phone);
CREATE INDEX
regression=# CREATE INDEX i2 ON test(phone, visible);
CREATE INDEX
regression=# CREATE INDEX i3 ON test(phone, visible) WHERE visible;
CREATE INDEX
regression=# analyze test;
ANALYZE
regression=# explain SELECT * FROM test WHERE phone='12345' AND visible;
QUERY PLAN
----------------------------------------------------------------
Index Scan using i3 on test (cost=0.00..5.82 rows=1 width=10)
Index Cond: ((phone = '12345'::text) AND (visible = true))
(2 rows)
regression=# drop index i3;
DROP INDEX
regression=# explain SELECT * FROM test WHERE phone='12345' AND visible;
QUERY PLAN
----------------------------------------------------------------
Index Scan using i2 on test (cost=0.00..5.82 rows=1 width=10)
Index Cond: ((phone = '12345'::text) AND (visible = true))
Filter: visible
(3 rows)
regression=#
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-03-10 18:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-10 16:45 one-field index vs. multi-field index planner estimates Evgeny Gridasov <eugrid@fpm.kubsu.ru>
2006-03-10 17:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-10 17:28 ` Re: one-field index vs. multi-field index planner Evgeny Gridasov <eugrid@fpm.kubsu.ru>
2006-03-10 18:58 ` Re: one-field index vs. multi-field index planner Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox