News for hackers. Stuff that matters.
Re:Using Redundant Conditions to Unlock Indexes in MySQL by web3-is-a-scam on Wednesday June 07, 2023 @02:03PM (#36230755)
Does this technique generally work with postgres as well?
Re:Using Redundant Conditions to Unlock Indexes in MySQL by mdpye on Wednesday June 07, 2023 @02:10PM (#36230863)
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...

Re:Using Redundant Conditions to Unlock Indexes in MySQL by aarondf on Wednesday June 07, 2023 @02:19PM (#36231021)
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.

Re:Using Redundant Conditions to Unlock Indexes in MySQL by berkle4455 on Wednesday June 07, 2023 @02:18PM (#36231004)
MySQL 8+ also supports functional indexes
Re:Using Redundant Conditions to Unlock Indexes in MySQL by wswope on Wednesday June 07, 2023 @03:05PM (#36231848)
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.)

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                                                                                                          │
    └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
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 ;).