From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Traster <ktraster@freshgrillfoods.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: index scan forward vs backward = speed difference of 357X slower!
Date: Wed, 08 Feb 2012 14:31:08 -0500
Message-ID: <29203.1328729468@sss.pgh.pa.us> (raw)
In-Reply-To: <CAC7CH7GB2Yi1VqQ9hy8Yex0avGvgLfvQkHabFGfmZa1WRU3X+A@mail.gmail.com>
References: <CAC7CH7GB2Yi1VqQ9hy8Yex0avGvgLfvQkHabFGfmZa1WRU3X+A@mail.gmail.com>
Kevin Traster <ktraster@freshgrillfoods.com> writes:
> 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.
Apparently, there are some rows passing the filter condition that are
close to the end of the index, but none that are close to the start.
So it takes a lot longer to find the first 15 matches in one case than
the other. You haven't shown us the index definition, but I gather from
the fact that the scan condition is just a Filter (not an Index Cond)
that the index itself doesn't offer any clue as to whether a given row
meets those conditions. So this plan is going to be doing a lot of
random-access heap probes until it finds a match.
> Why and how do I fix it?
Probably, you need an index better suited to the query condition.
If you have one and the problem is that the planner's not choosing it,
then this is going to take more information to resolve.
regards, tom lane
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: tgl@sss.pgh.pa.us, ktraster@freshgrillfoods.com
Subject: Re: index scan forward vs backward = speed difference of 357X slower!
In-Reply-To: <29203.1328729468@sss.pgh.pa.us>
* 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