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@xxxxxxxxxxxxx> wrote: > Kevin Traster <ktraster@xxxxxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
