Re:Using Redundant Conditions to Unlock Indexes in MySQL
by aarondf on Wednesday June 07, 2023 @03:10PM
(#36231929)
That's a great use case!
Re:Using Redundant Conditions to Unlock Indexes in MySQL
by sa46 on Wednesday June 07, 2023 @04:19PM
(#36232952)
> 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?
Re:Using Redundant Conditions to Unlock Indexes in MySQL
by wswope on Wednesday June 07, 2023 @04:46PM
(#36233304)
Yeah, 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.htm...
Re:Using Redundant Conditions to Unlock Indexes in MySQL
by luhn on Wednesday June 07, 2023 @04:38PM
(#36233208)
You 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 │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
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.)