From: | Kevin Traster <ktraster(at)freshgrillfoods(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | index scan forward vs backward = speed difference of 357X slower! |
Date: | 2012-02-07 16:49:05 |
Message-ID: | CAC7CH7GB2Yi1VqQ9hy8Yex0avGvgLfvQkHabFGfmZa1WRU3X+A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Ofer Israeli | 2012-02-07 17:27:33 | Re: Inserts or Updates |
Previous Message | Igor Neyman | 2012-02-07 15:11:37 | Re: Index with all necessary columns - Postgres vs MSSQL |