From: | maxim(dot)boguk(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #9135: PostgreSQL doesn't want use index scan instead of (index scan+sort+limit) |
Date: | 2014-02-07 04:55:18 |
Message-ID: | 20140207045518.13610.2870@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 9135
Logged by: Maxim Boguk
Email address: maxim(dot)boguk(at)gmail(dot)com
PostgreSQL version: 9.3.2
Operating system: Linux
Description:
Hi,
One of my customers have very curious situation with simple query and index
usage. I tried different ideas but it doesn't work anyway and now I out of
ideas. It's looks like a bug if I not missing something.
Detail:
4GB liexWebmasterProducts table with interesting fields:
lwpid | integer | not null
default nextval('liexwebmasterproducts_lwpid_seq'::regclass)
lwpname | text |
...
lwpwebsiteid | integer |
...
lwpnotforsale | boolean | not null
...
lwpcreatedate | timestamp without time zone | not null
default now()
...
Index on the last three fields defined as:
"i_liexwebmasterproducts_2" btree (lwpwebsiteid, lwpnotforsale,
lwpcreatedate)
Target query and plan:
select *
from liexWebmasterProducts this_
where
this_.lwpWebsiteId=5935
and this_.lwpnotForSale=FALSE
order by this_.lwpCreateDate desc limit 1;
Limit (cost=122.18..122.19 rows=1 width=902) (actual time=13.505..13.506
rows=1 loops=1)
-> Sort (cost=122.18..124.57 rows=953 width=902) (actual
time=13.503..13.503 rows=1 loops=1)
Sort Key: lwpcreatedate
Sort Method: top-N heapsort Memory: 27kB
-> Index Scan using i_liexwebmasterproducts_2 on
liexwebmasterproducts this_ (cost=0.43..117.42 rows=953 width=902) (actual
time=0.171..10.429 rows=1674 loops=1)
Index Cond: ((lwpwebsiteid = 5935) AND (lwpnotforsale =
false))
Filter: (NOT lwpnotforsale)
Total runtime: 13.626 ms
I have no idea why Postgresql doesn't want use simple index scan over 3
fields...
set enable_sort to 0;
have no effect:
Limit (cost=10000000119.90..10000000119.90 rows=1 width=902) (actual
time=6.591..6.592 rows=1 loops=1)
-> Sort (cost=10000000119.90..10000000122.24 rows=935 width=902)
(actual time=6.588..6.588 rows=1 loops=1)
Sort Key: lwpcreatedate
Sort Method: top-N heapsort Memory: 27kB
-> Index Scan using i_liexwebmasterproducts_2 on
liexwebmasterproducts this_ (cost=0.43..115.22 rows=935 width=902) (actual
time=0.050..3.733 rows=1673 loops=1)
Index Cond: ((lwpwebsiteid = 5935) AND (lwpnotforsale =
false))
Filter: (NOT lwpnotforsale)
Total runtime: 6.670 ms
It seems somehow related to the: "Filter: (NOT lwpnotforsale)" part of the
query which look like redundant, but it's my pure guessing.
Reindexing the index, vacuum analyze table - provide zero effect on the
plan.
Generating whole new subset and table via:
shop=# create table test as select lwpid,lwpwebsiteid, lwpnotforsale,
lwpcreatedate from liexwebmasterproducts;
SELECT 6799176
shop=# create index CONCURRENTLY test_index_1 on test(lwpwebsiteid,
lwpnotforsale, lwpcreatedate);
CREATE INDEX
shop=# vacuum analyze test;
VACUUM
Have no effect as well (plan over test table stay the same).
Changing order of the two first fields in index via:
create index CONCURRENTLY test_index_2 on test(lwpnotforsale, lwpwebsiteid,
wpcreatedate);
have no effect on the plan too.
Kindly Regards,
Maksym
From | Date | Subject | |
---|---|---|---|
Next Message | Harry Rossignol | 2014-02-07 05:13:38 | Re: BUG #9135: PostgreSQL doesn't want use index scan instead of (index scan+sort+limit) |
Previous Message | Alexander Hill | 2014-02-06 18:07:59 | Re: BUG #8354: stripped positions can generate nonzero rank in ts_rank_cd |