From: Rural Hunter <ruralhunter@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: index scan forward vs backward = speed difference of 357X slower!
Date: Wed, 08 Feb 2012 22:36:30 +0800
Message-ID: <4F32886E.1030907@gmail.com> (raw)
In-Reply-To: <CAC7CH7GB2Yi1VqQ9hy8Yex0avGvgLfvQkHabFGfmZa1WRU3X+A@mail.gmail.com>
References: <CAC7CH7GB2Yi1VqQ9hy8Yex0avGvgLfvQkHabFGfmZa1WRU3X+A@mail.gmail.com>
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?
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ruralhunter@gmail.com
Subject: Re: index scan forward vs backward = speed difference of 357X slower!
In-Reply-To: <4F32886E.1030907@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox