Re: now() and ::date
От | Tom Lane |
---|---|
Тема | Re: now() and ::date |
Дата | |
Msg-id | 28626.1041031266@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | now() and ::date (Ray Ontko <rayo@ontko.com>) |
Список | pgsql-admin |
Ray Ontko <rayo@ontko.com> writes: > I'm having a little trouble understanding the query optimizer > related to a timestamp with time zone column. I have a table > called "event" with a not null column called "event_date_time" > of type "timestamp with time zone" that has been recently analyzed. > My basic questions are: > Why does "now()" disqualify use of the index? > Why does "::date" disqualify use of the index? The short answer: update to 7.3.1, you'll be much happier. The long answer: indexscans can only use comparisons against values that will be constant for the duration of the scan. For example, select * from foo where x > random(); couldn't be implemented as an indexscan without changing its behavior. The conceptual model is that random() is recomputed at each row and compared to that row's value of x, and indeed that's how we have to do it. Prior to 7.3, the planner only understood two categories of function: constant forever ("cachable") and totally unpredictable (not "cachable"). Any WHERE clause containing an occurrence of the second category of function is considered un-indexable. Unfortunately, now() clearly doesn't fall in the first category, so it had to be treated as the second category. For 7.3 we've invented a third function category, which is "constant for any one command" --- and now() is defined in a way that lets it be put in that category. (To wit: its value doesn't advance inside a transaction.) So in 7.3 your query is considered indexable: regression=# explain select count(*) from event regression-# where event_date_time >= now()::timestamp with time zone - '1 month'::interval regression-# and event_date_time < now()::timestamp with time zone; QUERY PLAN ------------------------------------------------------------------------------------------------------ Aggregate (cost=17.13..17.13 rows=1 width=0) -> Index Scan using event_date_time on event (cost=0.00..17.12 rows=5 width=0) Index Cond: ((event_date_time >= (now() - '1 mon'::interval)) AND (event_date_time < now())) (3 rows) The ::date case turns out to be the same issue, in a more subtle guise. When you write the query like that, you end up invoking the date-to-timestamp-with-time-zone conversion function. This function's results depend on the TimeZone system parameter, and therefore it doesn't fall in the "constant forever" category either. Before 7.3, we couldn't index it. In 7.3 you can see exactly what's going on: regression=# explain select count(*) from event regression-# where event_date_time >= '2002-12-25'::date - '1 month'::interval regression-# and event_date_time < '2002-12-25'::date; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=17.12..17.12 rows=1 width=0) -> Index Scan using event_date_time on event (cost=0.00..17.11 rows=5 width=0) Index Cond: ((event_date_time >= ('2002-11-25 00:00:00'::timestamp without time zone)::timestamp with time zone)AND (event_date_time < ('2002-12-25'::date)::timestamp with time zone)) (3 rows) Notice how the computation '2002-12-25'::date - '1 month'::interval --- which is interpreted as "promote date to timestamp without time zone and do timestamp without time zone minus interval" --- is folded to a constant, but the final conversion to timestamp with time zone is left to be done at runtime. The foldable part isn't dependent on TimeZone and so it's in the "constant forever" category, letting the planner reduce it on sight. If you don't want to update to 7.3 right now, there are tricks you can play to fool the planner into treating these queries as indexable. Basically you stick the noncachable computations inside a user-defined function that you mark cachable (which is a lie, but you can get away with it if you are careful about how you use the function). See the PG list archives for previous discussions of this issue. I'd recommend updating, instead... regards, tom lane
В списке pgsql-admin по дате отправления: