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

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?

Responses

Browse pgsql-performance by date

  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