Re: one-field index vs. multi-field index planner

Evgeny Gridasov <eugrid(at)fpm(dot)kubsu(dot)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