Sequential scan from simple query

"Leif B. Kristensen" <leif@solumslekt.org> writes:

Can anybody tell me why the following query requires a full table scan?
pgslekt=> explain select person_id, last_edit from persons
order by last_edit desc, person_id desc limit 50;

'persons' is a regular table where person_id is the primary
key. 'last_edit' is a regular date field. I've also tried:
pgslekt=> create index last_edit_key on persons(last_edit);
But that doesn't make any difference.

Neither of those indexes can provide the requested two-column ordering.

regards, tom lane

On Wednesday 23. May 2007 16:31, Leif B. Kristensen wrote:

I've also tried:

pgslekt=> create index last_edit_key on persons(last_edit);

But that doesn't make any difference.

But this one did:

pgslekt=> create index last_edited_persons_key on
persons(last_edit,person_id);
CREATE INDEX
pgslekt=> explain select person_id, last_edit from persons
order by last_edit desc, person_id desc limit 50;
QUERY PLAN
-------------------------------------------------------------------
Limit (cost=0.00..3.81 rows=50 width=8)
-> Index Scan Backward using last_edited_persons_key on persons
(cost=0.00..1230.10 rows=16123 width=8)
(2 rows)

Doh ...
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/