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

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!


> 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.
> --
>   Richard Huxton
>   Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to