From: Tom Lane <tgl@sss.pgh.pa.us>
To: Evgeny Gridasov <eugrid@fpm.kubsu.ru>
Cc: pgsql-performance@postgresql.org
Subject: Re: one-field index vs. multi-field index planner
Date: Fri, 10 Mar 2006 13:58:50 -0500
Message-ID: <7570.1142017130@sss.pgh.pa.us> (raw)
In-Reply-To: <20060310202823.88495774.eugrid@fpm.kubsu.ru>
References: <6BCB9D8A16AC4241919521715F4D8BCEA35109@algol.sollentuna.se>
<200603101113.35868.jdevisser@digitalfairway.com>
<20060310194545.be002083.eugrid@fpm.kubsu.ru>
<6628.1142010559@sss.pgh.pa.us>
<20060310202823.88495774.eugrid@fpm.kubsu.ru>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, eugrid@fpm.kubsu.ru
Subject: Re: one-field index vs. multi-field index planner
In-Reply-To: <7570.1142017130@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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