postgresql indexing postgresql-performance volatility

How to use date_trunc() with timestamptz in an index to support a join?

I have 2 tables in my database as defined below:

CREATE TABLE metric_events (
    id uuid PRIMARY KEY,
    metric_id integer NOT NULL,
    event_at timestamp with time zone NOT NULL,
    code text NOT NULL, 
    test boolean DEFAULT false NOT NULL,
    success boolean DEFAULT false NOT NULL
);

CREATE TABLE hourly_metric_event_counters (
    start_at timestamp with time zone NOT NULL,
    metric_id integer NOT NULL,
    code text NOT NULL,
    test boolean NOT NULL,
    total_count integer DEFAULT 0,
    success_count integer DEFAULT 0,
    PRIMARY KEY (metric_id, test, code, start_at)
);

CREATE UNIQUE INDEX metric_events_uuid_index ON metric_events(id);
CREATE INDEX metric_events_event_at_index on metric_events(event_at);
CREATE INDEX metric_events_metric_id_and_event_at_index ON metric_events(metric_id, event_at);
CREATE INDEX hourly_metric_event_counters_start_at_index ON hourly_metric_event_counters(start_at);

I run this query once daily:

SELECT date_trunc('hour', b.event_at),
       b.metric_id,
       b.code,
       b.test,
       COUNT(b.*) - MAX(bec.total_count) as total_diff,
       SUM(CASE WHEN b.success THEN 1 ELSE 0 END) - MAX(bec.success_count) as success_diff
FROM metric_events b
JOIN hourly_metric_event_counters bec
ON bec.metric_id = b.metric_id AND
   bec.test = b.test AND
   bec.code = b.code AND
   bec.start_at = date_trunc('hour', b.event_at)
WHERE event_at >= date_trunc('hour', NOW() - interval '24 hours') AND event_at < date_trunc('hour', NOW() - interval '1 hour')
GROUP BY 1, 2, 3, 4
HAVING COUNT(b.*) - MAX(bec.total_count) != 0 OR
       SUM(CASE WHEN b.success THEN 1 ELSE 0 END) - MAX(bec.success_count) != 0

I am trying to create an index on metric_events for columns metric_id, date_trunc('hour', event_at), test, code to make the join faster:

CREATE INDEX metric_events_composite_index on metric_events (metric_id, code, test, success, date_trunc('hour'::text, event_at));

However:

ERROR: functions in index expression must be marked IMMUTABLE SQL state: 42P17

Is there a way?

Solution

There are multiple (overloaded) versions of the function date_trunc(). All of them are IMMUTABLE - except the one taking timestamptz input which is (necessarily) only STABLE. Some of the invocations (like date_trunc('day', event_at)) depend on the current timezone setting. There are even (incredibly stupid) time shifts involving fractions of an hour for the (equally stupid) "daylight saving time". So even truncating a point in time to the hour can depend on the time zone and time of the year.

Accordingly, you cannot create an index on date_trunc('hour', event_at). The expression is not IMMUTABLE. Casting to time or timestamp is also just STABLE for similar reasons. So an index involving date_trunc('hour', (event_at::time)) is also not possible.

Workarounds include:

Switch to timestamp without time zone. (Only if appropriate - in most cases you want timestamp with time zone!)
Add a generated column with the truncated timestamp and use that in the index. See:
- Computed / calculated / virtual / derived / generated columns in PostgreSQL
Create a (fake) immutable wrapper function and use that in index and query. Make sure it's only used where the result is, de facto, immutable, or bad things will happen. Example:
- Index on timestamp: Functions in index expression must be marked as IMMUTABLE

That said, I doubt your index would be much help, or even be used to begin with.

If you were hoping for index-only scans, then move success to the INCLUDE section of the index. (It's not used for filtering or sorting.) You would also have to include the original column event_at to make index-only scans possible (a weakness of the query planner):

CREATE INDEX metric_events_composite_index
ON metric_events (metric_id, code, test, fake_immutable_date_trunc('hour', event_at))
INCLUDE (success, event_at);

I am not sure the join allows index usage to begin with.

See: