Re: BUG #9135: PostgreSQL doesn't want use index scan instead of (index scan+sort+limit)
От | Harry Rossignol |
---|---|
Тема | Re: BUG #9135: PostgreSQL doesn't want use index scan instead of (index scan+sort+limit) |
Дата | |
Msg-id | 52F46B82.2000805@comcast.net обсуждение исходный текст |
Ответ на | BUG #9135: PostgreSQL doesn't want use index scan instead of (index scan+sort+limit) (maxim.boguk@gmail.com) |
Ответы |
Re: BUG #9135: PostgreSQL doesn't want use index scan instead
of (index scan+sort+limit)
|
Список | pgsql-bugs |
I would try - select * from liexWebmasterProducts this_ where this_.lwpWebsiteI_.lwpnotForSale lwpWebsiteId,.lwpCreateDate desc limit 1; 2/6/2014 8:55 PM, maxim.boguk@gmail.com wrote: > The following bug has been logged on the website: > > Bug reference: 9135 > Logged by: Maxim Boguk > Email address: maxim.boguk@gmail.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 > > > > >
В списке pgsql-bugs по дате отправления:
Предыдущее
От: maxim.boguk@gmail.comДата:
Сообщение: BUG #9135: PostgreSQL doesn't want use index scan instead of (index scan+sort+limit)
Следующее
От: Harry RossignolДата:
Сообщение: Re: BUG #9135: PostgreSQL doesn't want use index scan instead of (index scan+sort+limit)