Diff size index casting

index_diff_cast_timestampz_date.sql

DROP TABLE IF EXISTS notifications;
CREATE TABLE notifications (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL
);

INSERT INTO
  notifications (created_at)
SELECT
  (LOCALTIMESTAMP - interval '1 month' * random())::timestamptz
FROM generate_series(1, 10000000) g; -- 10 millions

CREATE INDEX idx_notifications_1 ON notifications(created_at);
CREATE INDEX idx_notifications_2 ON notifications (((created_at at time zone 'UTC')::date));

SELECT
    psai.indexrelname                              AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM
    pg_tables t
    LEFT JOIN pg_class c ON t.tablename = c.relname
    LEFT JOIN pg_index i ON c.oid = i.indrelid
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE
    t.schemaname NOT IN ('pg_catalog', 'information_schema')
AND t.tablename = 'notifications'
ORDER BY 1, 2;

/*
idx_notifications_1	206 MB
idx_notifications_2	66 MB
notifications_pkey	214 MB
*/