Re: "Interesting" query planning for timestamp ranges in where clause?

rvigmbdc(at)umail(dot)furryterror(dot)org (Zygo Blaxell) writes:
> Column | Type | Modifiers | Description
> --------+--------------------------------+-----------+-------------
> t | timestamp(0) without time zone | not null |
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

> ilt=# explain analyze select time_bucket(t, 32), avg(t1), avg(t2), avg(t3), avg(t4), avg(t5), avg(t6), avg(t7), avg(t8) from temp where t between now() - interval '1 day' and now() group by time_bucket(t, 32) order by time_bucket(t, 32) desc;

> -> Seq Scan on "temp" (cost=0.00..207797.15 rows=30620 width=72) (actual time=35275.231..38323.524 rows=85360 loops=1)
> Filter: (((t)::timestamp with time zone >= (now() - '1 day'::interval)) AND ((t)::timestamp with time zone <= now()))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

This is ye olde bog-standard "can't index a cross-datatype comparison"
problem. Personally I think you probably want your t column to be
timestamp with tz --- it's highly annoying that the SQL spec says
unadorned "timestamp" must mean "timestamp without time zone".
But if you really want it without tz, cast the result of now() to
timestamp without tz, or use LOCALTIMESTAMP instead of now().

regards, tom lane