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

Поиск
Список
Период
Сортировка
От Shaun Thomas
Тема Re: multiple joins + Order by + LIMIT query performance issue
Дата
Msg-id 1210097669.14833.45.camel@berners-lee
обсуждение исходный текст
Ответ на Re: multiple joins + Order by + LIMIT query performance issue  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
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



В списке pgsql-performance по дате отправления:

Предыдущее
От: "Heikki Linnakangas"
Дата:
Сообщение: Re: multiple joins + Order by + LIMIT query performance issue
Следующее
От: Shaun Thomas
Дата:
Сообщение: Re: multiple joins + Order by + LIMIT query performance issue