Indexes

[Primoz]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Primoz <pr****@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match