Hacker News Reader

Comment by web3-is-a-scam | Hacker News Reader

Does this technique generally work with postgres as well?

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.)

That's a great use case!

> 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?

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...

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.

Yeah, crappy example on my end - thank you for fleshing out the details ;).

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...

MySQL 8+ also supports functional indexes

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.

Reply on news.ycombinator.com