Re: multiple joins + Order by + LIMIT query performance issue

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