Re: Performance problems on a fairly big table with two

Richard,

Thanks a lot! You were right - the query parser "misunderstood"
now() - '1 day'::interval and only used one of the indexes (as I already
noticed).

Actually all I had to do was to cast the result like this:

(now() - '1 day'::interval)::date

75s is not between 10ms and 200ms.

Thanks again!

-ra


> 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
>