3 ms·
Yeah, TIMESTAMPTZ expressions don’t index nicely because they’re nondeterministic (since they depend on session time zone). So if you’re running heavy time-seri
by wswope 3y ago
Yeah, TIMESTAMPTZ expressions don’t index nicely because they’re nondeterministic (since they depend on session time zone). So if you’re running heavy time-series queries, you can speed them up by adding a predicate that’ll use a AT TIME ZONE ‘UTC’ expression, which is more easily indexable.
E.g.:
WHERE event_timestamp >= NOW() - ‘30 days’::INTERVAL
AND (event_timestamp AT TIME ZONE ‘UTC’)::DATE >= NOW() - ‘31 days’::INTERVAL
(^This particular example probably has a better solution, but I’ve used the same pattern for DATE_TRUNC expressions and seen dramatic improvements.)
- aarondf 3y agoThat's a great use case!
- sa46 3y ago> Yeah, TIMESTAMPTZ expressions don’t index nicely because they’re nondeterministic Could you expand on why it's not deterministic? I thought timestamptz was 8-byte microseconds since the epoch. Is the problem that the timestamptz uses the server timezone?
- wswope 3y agoYeah, you've got the right idea. Specifically, functions that operate on TIMESTAMPTZ aren't typically marked as IMMUTABLE (because their output implicitly depends on the connection-level time zone setting), and that IMMUTABLE flag is required for functions used in indexed expressions. https://www.postgresql.org/docs/current/xfunc-volatility.html https://www.postgresql.org/docs/current/xfunc-volatility.htm...
- luhn 3y agoYou are correct that date_trunc will not utilize an index. To the query planner that's an opaque function; it has no idea how to translate that to an index condition. However, the example you gave works just fine. It's all just UTC in backend and very much deterministic. postgres=# create table timetest as select t.time, 'hi' as foo from generate_series('2000-01-01'::timestamptz, '2010-01-01'::timestamptz, '5 minutes'::interval) t(time); SELECT 1052065 postgres=# create index on timetest(time); CREATE INDEX postgres=# explain analyze select * from timetest where time > now() - '30 days'::interval; ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Index Scan using timetest_time_idx on timetest (cost=0.43..4.45 rows=1 width=11) (actual time=0.019..0.019 rows=0 loops=1) │ │ Index Cond: ("time" > (now() - '30 days'::interval)) │ │ Planning Time: 1.163 ms │ │ Execution Time: 0.081 ms │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ If you query frequently with date_trunc, you may want to use an expression index: postgres=# create index on timetest(date_trunc('day', time at time zone 'US/Pacific')); CREATE INDEX postgres=# explain analyze select * from timetest where date_trunc('day', time at time zone 'US/Pacific') > now() - '30 days'::interval; ┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Index Scan using timetest_date_trunc_idx on timetest (cost=0.43..4.45 rows=1 width=11) (actual time=0.075..0.076 rows=0 loops=1) │ │ Index Cond: (date_trunc('day'::text, timezone('US/Pacific'::text, "time")) > (now() - '30 days'::interval)) │ │ Planning Time: 0.379 ms │ │ Execution Time: 0.124 ms │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ Note that to make this work you have to convert "time" to a timestamp (no tz), because date_trunc with timestamptz is not an immutable function and cannot be indexed—I believe this is what you may have been thinking of when you say that timestamptz is non-deterministic.
- wswope 3y agoYeah, crappy example on my end - thank you for fleshing out the details ;).