Typo: Work_mem = 32 MB
The definition for both column and index:
shareschange | numeric |
"changes_shareschange" btree (shareschange)
Index created using: CREATE INDEX changes_shareschange ON changes(shareschange);
The entire table is created nightly (and analyzed afterwords), and
used only for reporting - there no updates/deletes, so there shouldn't
be any dead rows in the table.
Likewise, there is no nulls in the column.
Please elaborate on:
>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
Are you saying it is the retrieval of the physically random located 15
rows to meet the ascending condition that causes the 5 sec difference?
The table is not-clustered, so it is "random" for descending also.
The condition is shareschange ascending, I have an index for that
condition and the planner is using it.
What else can I look at?
On Wed, Feb 8, 2012 at 11:31 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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