Re: Performance problems on a fairly big table with two key columns.

On Thursday 04 September 2003 23:53, Rasmus Aveskogh wrote:
> Hi,
>
> I have a table that looks like this:
>
> DATA ID TIME
>
> |------|----|------|
>
> The table holds app. 14M rows now and grows by app. 350k rows a day.
>
> The ID-column holds about 1500 unique values (integer).
> The TIME-columns is of type timestamp without timezone.
>
> I have one index (b-tree) on the ID-column and one index (b-tree) on the
> time-column.
>
> My queries most often look like this:
>
> SELECT DATA FROM <tbl> WHERE ID = 1 AND TIME > now() - '1 day'::interval;
[snip]
> I tried applying a multicolumn index on ID and TIME, but that one won't
> even be used (after ANALYZE).

The problem is likely to be that the parser isn't spotting that now()-'1 day'
is constant. Try an explicit time and see if the index is used. If so, you
can write a wrapper function for your expression (mark it STABLE so the
planner knows it won't change during the statement).

Alternatively, you can do the calculation in the application and use an
explicit time.

HTH
--
Richard Huxton
Archonet Ltd