Re: index being ignored for "limit n" queries

Piotr Sulecki <Piotr(dot)Sulecki(at)sybilla(dot)traxelektronik(dot)pl> writes:
> I have two really big tables, the problem is with one of them. The table
> looks as follows:
> ...
> Indexes:
> "pakiety_pkey" PRIMARY KEY, btree (pktid)
> "pakiety_stid_received_idx" UNIQUE, btree (stid, received)
> "pakiety_measured_idx" btree (measured)
> "pakiety_received_idx" btree (received)
> "pakiety_stid_measured_idx" btree (stid, measured)

> trax=# explain select * from pakiety where stid = 234::smallint order by
> received desc limit 1;

The reason this won't use the (stid, received) index is that the
requested sort order doesn't match that index. Try

select * from pakiety where stid = 234::smallint
order by stid desc, received desc
limit 1;

There is code in CVS tip to recognize that the equality constraint on
stid allows the index to be considered as producing data ordered by
received alone --- but no released version will make that deduction
for you.

regards, tom lane