On Tue, Apr 05, 2011 at 11:35:29PM +0100, Thom Brown wrote: > I'm using 9.1dev. > > Could someone explain the following behaviour? > > -- create a test table > CREATE TABLE indextest (id serial, stuff text); > > -- insert loads of values with intermittent sets of less common values > INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000); > INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000); > INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000); > INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000); > INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000); > INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000); > INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000); > INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000); > INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000); > INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000); > INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000); > INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000); > INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000); > INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000); > INSERT INTO indextest (stuff) SELECT 'meow' FROM generate_series (1,1000000); > INSERT INTO indextest (stuff) SELECT 'bark' FROM generate_series (1,1000); > > -- create regular index > CREATE INDEX indextest_stuff ON indextest(stuff); > > -- update table stats > ANALYZE indextest; > > postgres=# explain analyze select * from indextest where stuff = 'bark'; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------- > Index Scan using indextest_stuff on indextest (cost=0.00..485.09 > rows=9076 width=9) (actual time=0.142..3.533 rows=8000 loops=1) > Index Cond: (stuff = 'bark'::text) > Total runtime: 4.248 ms > (3 rows) > > This is very fast. Now if I drop the index and add a partial index > with the conditions being tested. > > DROP INDEX indextest_stuff; > > CREATE INDEX indextest_stuff ON indextest(stuff) WHERE stuff = 'bark'; > > postgres=# explain analyze select * from indextest where stuff = 'bark'; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------- > Seq Scan on indextest (cost=0.00..143386.48 rows=5606 width=9) > (actual time=164.321..1299.794 rows=8000 loops=1) > Filter: (stuff = 'bark'::text) > Total runtime: 1300.267 ms > (3 rows) > > The index doesn't get used. There's probably a logical explanation, > which is what I'm curious about. > The stats seem off. Are you certain that an analyze has run? Cheers, Ken -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
