PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 64-bit
Dedicated DB server
4GB ram
Shared_Buffers = 1 GB
Effective_cache_size = 3GB
Work_mem = 32GB
Analyze done
Queries ran multiple times, same differences/results
Default Statistics = 1000
Query (5366ms) :
explain analyze select initcap (fullname), initcap(issuer),upper(rsymbol), initcap(industry), activity,to_char(shareschange,'FM9,999,999,999,999,999'),sharespchange ||+ E'\%' from changes where activity in (4,5) and mfiled >= (select max(mfiled) from changes) order by shareschange asc limit 15
Slow Ascending explain Analyze:
http://explain.depesz.com/s/zFz
Query (15ms) :
explain analyze select initcap (fullname), initcap(issuer),upper(rsymbol), initcap(industry), activity,to_char(shareschange,'FM9,999,999,999,999,999'),sharespchange ||+ E'\%' from changes where activity in (4,5) and mfiled >= (select max(mfiled) from changes) order by shareschange desc limit 15
Fast descending explain analyze:
http://explain.depesz.com/s/OP7
The index: changes_shareschange is a btree index created with default ascending order
The query plan and estimates are exactly the same, except desc has index scan backwards instead of index scan for changes_shareschange.
Yet, actual runtime performance is different by 357x slower for the ascending version instead of descending.
Why and how do I fix it?
