what's the size of the index? is it too big to fit in shared_buffers?
maybe the firt 15 rows by asc order are in buffer but the ones of desc
order are not, while your disk IO is very slow?
btw, your mem configuration of work_men is very strange.
于 2012/2/8 0:49, Kevin Traster 写道:
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?
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
