4 ms·

Does this technique generally work with postgres as well?

mdpye 3y ago
I haven't checked if this works in mysql, but in postgres I would certainly reach for an index on the computed values, which it supports. Index the thing you're querying on...
- berkle4455 3y ago
  MySQL 8+ also supports functional indexes
- aarondf 3y ago
  Totally agree! From the article: > The optimal indexing strategy always depends on the application, but in general, it's best to have indexes on the conditions you are frequently querying against. [...] This makes redundant conditions useful for queries that are only sometimes run or where indexes can't be easily added to the main conditions.
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 ago
  That'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 ago
    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.html https://www.postgresql.org/docs/current/xfunc-volatility.htm...
- luhn 3y ago
  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 │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ 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.

I haven't checked if this works in mysql, but in postgres I would certainly reach for an index on the computed values, which it supports. Index the thing you're querying on...

berkle4455 3y ago
MySQL 8+ also supports functional indexes
aarondf 3y ago
Totally agree! From the article: > The optimal indexing strategy always depends on the application, but in general, it's best to have indexes on the conditions you are frequently querying against. [...] This makes redundant conditions useful for queries that are only sometimes run or where indexes can't be easily added to the main conditions.

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 ago
That'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 ago
  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.html https://www.postgresql.org/docs/current/xfunc-volatility.htm...
luhn 3y ago
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 │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ 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.