From: Kenneth Marshall <ktm@rice.edu>
To: Thom Brown <thom@linux.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Partial index slower than regular index
Date: Tue, 5 Apr 2011 18:02:51 -0500
Message-ID: <20110405230251.GD26726@aart.is.rice.edu> (raw)
In-Reply-To: <BANLkTimFZjYve4GDOXQJ6VjRO1pOQEwp4w@mail.gmail.com>
References: <BANLkTimFZjYve4GDOXQJ6VjRO1pOQEwp4w@mail.gmail.com>
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
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: ktm@rice.edu, thom@linux.com
Subject: Re: Partial index slower than regular index
In-Reply-To: <20110405230251.GD26726@aart.is.rice.edu>
* 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