Re: one-field index vs. multi-field index planner - Mailing list pgsql-performance

From Tom Lane
Subject Re: one-field index vs. multi-field index planner
Date
Msg-id 7570.1142017130@sss.pgh.pa.us
Whole thread Raw
In response to Re: one-field index vs. multi-field index planner  (Evgeny Gridasov <eugrid@fpm.kubsu.ru>)
List 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

pgsql-performance by date:

Previous
From: "Magnus Hagander"
Date:
Subject: Re: Hanging queries on dual CPU windows
Next
From: Jan de Visser
Date:
Subject: Re: Hanging queries on dual CPU windows