Thread: Indexes

Hello,

is there any way to speedup queryes like

select ... from [table a] where [int field b] = [number] order by [datetime
field c] desc limit [number];

with index on field b I get something, but pgsql afterwards resolves to
sorting manually.

partial indices with index on field c where field b = number works, but are
dirty and are becoming annoying as number of different values of field b
grows.

Any suggestions?

Primoz

Primoz <primoz@slo-tech.com> writes:
> is there any way to speedup queryes like

> select ... from [table a] where [int field b] = [number] order by [datetime
> field c] desc limit [number];

For reasonably-small values of the LIMIT, what you want is
    (a) a two-column index on (b,c)
    (b) change the ORDER BY to
        ORDER BY b DESC, c DESC

See the pgsql-performance archives for more discussion.

            regards, tom lane