On Tue, 2008-05-06 at 18:59 +0100, Tom Lane wrote: > Whether the scan is forwards or backwards has nothing > to do with it. The planner is using the index ordering > to avoid having to do a full-table scan and sort. Oh, I know that. I just noticed that when this happened to us, more often than not, it was a reverse index scan that did it. The thing that annoyed me most was when it happened on an index that, even on a table having 20M rows, the cardinality is < 10 on almost every value of that index. In our case, having a "LIMIT 1" was much worse than just getting back 5 or 10 rows and throwing away everything after the first one. > but when it's a win it can be a big win, too, so "it's > a bug take it out" is an unhelpful opinion. That's just it... it *can* be a big win. But when it's a loss, you're index-scanning a 20M+ row table for no reason. We got around it, obviously, but it was a definite surprise when a query that normally runs in 0.5ms time randomly and inexplicably runs at 4-120s. This is disaster for a feed loader chewing through a few ten-thousand entries. But that's just me grousing about not having query hints or being able to tell Postgres to never, ever, ever index-scan certain tables. :) -- Shaun Thomas Database Administrator Leapfrog Online 807 Greenwood Street Evanston, IL 60201 Tel. 847-440-8253 Fax. 847-570-5750 www.leapfrogonline.com
