pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPartial index slower than regular index
11+ messages / 5 participants
[nested] [flat]
* Partial index slower than regular index
@ 2011-04-05 22:35 Thom Brown <thom@linux.com>
0 siblings, 3 replies; 11+ messages in thread
From: Thom Brown @ 2011-04-05 22:35 UTC (permalink / raw)
To: pgsql-performance
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.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company1
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Partial index slower than regular index
@ 2011-04-05 23:02 Kenneth Marshall <ktm@rice.edu>
parent: Thom Brown <thom@linux.com>
2 siblings, 1 reply; 11+ messages in thread
From: Kenneth Marshall @ 2011-04-05 23:02 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Partial index slower than regular index
@ 2011-04-05 23:31 Scott Marlowe <scott.marlowe@gmail.com>
parent: Thom Brown <thom@linux.com>
2 siblings, 1 reply; 11+ messages in thread
From: Scott Marlowe @ 2011-04-05 23:31 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: pgsql-performance
On Tue, Apr 5, 2011 at 4:35 PM, Thom Brown <thom@linux.com> wrote:
> I'm using 9.1dev.
SNIP
> 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.
Works fine for me:
explain analyze select * from indextest where stuff = 'bark';
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using indextest_stuff on indextest (cost=0.00..837.01
rows=13347 width=9) (actual time=0.226..6.073 rows=8000 loops=1)
Index Cond: (stuff = 'bark'::text)
Total runtime: 7.527 ms
Even with a random_page_cost = 4 it works. Running 8.3.13 btw.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Partial index slower than regular index
@ 2011-04-05 23:40 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Mark Kirkwood @ 2011-04-05 23:40 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Thom Brown <thom@linux.com>; pgsql-performance
On 06/04/11 11:31, Scott Marlowe wrote:
> On Tue, Apr 5, 2011 at 4:35 PM, Thom Brown<thom@linux.com> wrote:
>> I'm using 9.1dev.
> SNIP
>
>> 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.
> Works fine for me:
>
> explain analyze select * from indextest where stuff = 'bark';
> QUERY
> PLAN
> ------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using indextest_stuff on indextest (cost=0.00..837.01
> rows=13347 width=9) (actual time=0.226..6.073 rows=8000 loops=1)
> Index Cond: (stuff = 'bark'::text)
> Total runtime: 7.527 ms
>
> Even with a random_page_cost = 4 it works. Running 8.3.13 btw.
>
I reproduce what Thom sees - using 9.1dev with default config settings.
Even cranking up effective_cache_size does not encourage the partial
index to be used.
Mark
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Partial index slower than regular index
@ 2011-04-06 02:03 Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
parent: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 0 replies; 11+ messages in thread
From: Mark Kirkwood @ 2011-04-06 02:03 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Thom Brown <thom@linux.com>; pgsql-performance
On 06/04/11 11:40, Mark Kirkwood wrote:
> On 06/04/11 11:31, Scott Marlowe wrote:
>> On Tue, Apr 5, 2011 at 4:35 PM, Thom Brown<thom@linux.com> wrote:
>>> I'm using 9.1dev.
>> SNIP
>>
>>> 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.
>> Works fine for me:
>>
>> explain analyze select * from indextest where stuff = 'bark';
>> QUERY
>> PLAN
>> ------------------------------------------------------------------------------------------------------------------------------------
>>
>> Index Scan using indextest_stuff on indextest (cost=0.00..837.01
>> rows=13347 width=9) (actual time=0.226..6.073 rows=8000 loops=1)
>> Index Cond: (stuff = 'bark'::text)
>> Total runtime: 7.527 ms
>>
>> Even with a random_page_cost = 4 it works. Running 8.3.13 btw.
>>
>
> I reproduce what Thom sees - using 9.1dev with default config
> settings. Even cranking up effective_cache_size does not encourage the
> partial index to be used.
>
>
However trying with 9.0 gives me the (expected) same 8.3 behaviour:
test=# CREATE INDEX indextest_stuff ON indextest(stuff) WHERE stuff =
'bark';
CREATE INDEX
test=# explain analyze select * from indextest where stuff = 'bark';
QUERY PLAN
--------------------------------------------------------------------------------------------
Index Scan using indextest_stuff on indextest (cost=0.00..284.20
rows=5873 width=9)
(actual
time=0.276..9.621 rows=8000 loops=1)
Index Cond: (stuff = 'bark'::text)
Total runtime: 16.621 ms
(3 rows)
regards
Mark
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Partial index slower than regular index
@ 2011-04-06 04:44 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thom Brown <thom@linux.com>
2 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2011-04-06 04:44 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: pgsql-performance
Thom Brown <thom@linux.com> writes:
> The index doesn't get used. There's probably a logical explanation,
> which is what I'm curious about.
Er ... it's broken?
It looks like the index predicate expression isn't getting the right
collation assigned, so predtest.c decides the query doesn't imply the
index's predicate. Too tired to look into exactly why right now, but
it's clearly bound up in all the recent collation changes.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Partial index slower than regular index
@ 2011-04-06 08:15 Thom Brown <thom@linux.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Thom Brown @ 2011-04-06 08:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 6 April 2011 05:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thom Brown <thom@linux.com> writes:
>> The index doesn't get used. There's probably a logical explanation,
>> which is what I'm curious about.
>
> Er ... it's broken?
>
> It looks like the index predicate expression isn't getting the right
> collation assigned, so predtest.c decides the query doesn't imply the
> index's predicate. Too tired to look into exactly why right now, but
> it's clearly bound up in all the recent collation changes.
Testing it again with very explicit collations, it still has issues:
CREATE INDEX indextest_stuff ON indextest(stuff COLLATE "en_GB.UTF-8")
WHERE stuff COLLATE "en_GB.UTF-8" = 'bark' COLLATE "en_GB.UTF-8";
postgres=# explain analyze select * from indextest where stuff collate
"en_GB.UTF-8" = 'bark' collate "en_GB.UTF-8";
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on indextest (cost=0.00..143387.00 rows=8312 width=9)
(actual time=163.759..1308.316 rows=8000 loops=1)
Filter: ((stuff)::text = 'bark'::text COLLATE "en_GB.UTF-8")
Total runtime: 1308.821 ms
(3 rows)
But I'm possibly missing the point here.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Partial index slower than regular index
@ 2011-04-06 08:33 Thom Brown <thom@linux.com>
parent: Kenneth Marshall <ktm@rice.edu>
0 siblings, 0 replies; 11+ messages in thread
From: Thom Brown @ 2011-04-06 08:33 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: pgsql-performance
On 6 April 2011 00:02, Kenneth Marshall <ktm@rice.edu> wrote:
> The stats seem off. Are you certain that an analyze has run?
>
> Cheers,
> Ken
>
Yes, an ANALYZE was definitely run against the table.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Partial index slower than regular index
@ 2011-04-07 06:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thom Brown <thom@linux.com>
0 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2011-04-07 06:37 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: pgsql-performance
Thom Brown <thom@linux.com> writes:
> On 6 April 2011 05:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It looks like the index predicate expression isn't getting the right
>> collation assigned, so predtest.c decides the query doesn't imply the
>> index's predicate. Too tired to look into exactly why right now, but
>> it's clearly bound up in all the recent collation changes.
> Testing it again with very explicit collations, it still has issues:
Yeah, any sort of collation-sensitive operator in an index WHERE clause
was just plain broken. Fixed now.
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Partial index slower than regular index
@ 2011-04-07 07:10 Thom Brown <thom@linux.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 11+ messages in thread
From: Thom Brown @ 2011-04-07 07:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 7 April 2011 07:37, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thom Brown <thom@linux.com> writes:
>> On 6 April 2011 05:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> It looks like the index predicate expression isn't getting the right
>>> collation assigned, so predtest.c decides the query doesn't imply the
>>> index's predicate. Too tired to look into exactly why right now, but
>>> it's clearly bound up in all the recent collation changes.
>
>> Testing it again with very explicit collations, it still has issues:
>
> Yeah, any sort of collation-sensitive operator in an index WHERE clause
> was just plain broken. Fixed now.
Thanks Tom.
You said in the commit message that an initdb isn't required, but is
there anything else since 20th March that would cause cluster files to
break compatibility? I'm now getting the following message:
toucan:postgresql thom$ pg_ctl start
server starting
toucan:postgresql thom$ FATAL: database files are incompatible with server
DETAIL: The database cluster was initialized with CATALOG_VERSION_NO
201103201, but the server was compiled with CATALOG_VERSION_NO
201104051.
HINT: It looks like you need to initdb.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Partial index slower than regular index
@ 2011-04-07 08:01 Thom Brown <thom@linux.com>
parent: Thom Brown <thom@linux.com>
0 siblings, 0 replies; 11+ messages in thread
From: Thom Brown @ 2011-04-07 08:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 7 April 2011 08:10, Thom Brown <thom@linux.com> wrote:
> On 7 April 2011 07:37, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Thom Brown <thom@linux.com> writes:
>>> On 6 April 2011 05:44, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> It looks like the index predicate expression isn't getting the right
>>>> collation assigned, so predtest.c decides the query doesn't imply the
>>>> index's predicate. Too tired to look into exactly why right now, but
>>>> it's clearly bound up in all the recent collation changes.
>>
>>> Testing it again with very explicit collations, it still has issues:
>>
>> Yeah, any sort of collation-sensitive operator in an index WHERE clause
>> was just plain broken. Fixed now.
>
> Thanks Tom.
>
> You said in the commit message that an initdb isn't required, but is
> there anything else since 20th March that would cause cluster files to
> break compatibility? I'm now getting the following message:
>
> toucan:postgresql thom$ pg_ctl start
> server starting
> toucan:postgresql thom$ FATAL: database files are incompatible with server
> DETAIL: The database cluster was initialized with CATALOG_VERSION_NO
> 201103201, but the server was compiled with CATALOG_VERSION_NO
> 201104051.
> HINT: It looks like you need to initdb.
Nevermind. This was caused by "Add casts from int4 and int8 to numeric.".
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2011-04-07 08:01 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-05 22:35 Partial index slower than regular index Thom Brown <thom@linux.com>
2011-04-05 23:02 ` Kenneth Marshall <ktm@rice.edu>
2011-04-06 08:33 ` Thom Brown <thom@linux.com>
2011-04-05 23:31 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-04-05 23:40 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2011-04-06 02:03 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2011-04-06 04:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-06 08:15 ` Thom Brown <thom@linux.com>
2011-04-07 06:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-07 07:10 ` Thom Brown <thom@linux.com>
2011-04-07 08:01 ` Thom Brown <thom@linux.com>
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