Re: index scan forward vs backward = speed difference of 357X slower!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux