On 2/6/2014 9:13 PM, Harry Rossignol wrote:
> I would try -
>
> select * from liexWebmasterProducts this_ where
> this_.lwpWebsiteI_.lwpnotForSale
> lwpWebsiteId,.lwpCreateDate desc
ORDER BY lwpWebsiteI,_.lwpnotForSale , lwpWebsiteId,.lwpCreateDate
> Limit 1;
>
>
>
>
> 2/6/2014 8:55 PM, maxim(dot)boguk(at)gmail(dot)com wrote:
>> 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
>>
>>
>>
>>
>>
>
>
>