News for hackers. Stuff that matters.
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                                                                                                          │
    └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Re:Using Redundant Conditions to Unlock Indexes in MySQL by wswope on Wednesday June 07, 2023 @04:50PM (#36233363)
Yeah, crappy example on my end - thank you for fleshing out the details ;).