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