postgres - estimate index size for timestamp column

Question

Have a postgres table, ENTRIES, with a 'made_at' column of type timestamp without time zone.

That table has a btree index on both that column and on another column (USER_ID, a foreign key):

btree (user_id, date_trunc('day'::text, made_at))

As you can see, the date is truncated at the 'day'. The total size of the index constructed this way is 130 MB -- there are 4,000,000 rows in the ENTRIES table.

QUESTION: How do I estimate the size of the index if I were to care for time to be up to the second? Basically, truncate timestamp at second rather than day (should be easy to do, I hope).

Answer 1

Interesting question! According to my investigation they will be the same size.

My intuition told me that there should be no difference between the size of your two indices, as timestamp types in PostgreSQL are of fixed size (8 bytes), and I supposed the truncate function simply zeroed out the appropriate number of least significant time bits, but I figured I had better support my guess with some facts.

I spun up a free dev database on heroku PostgreSQL and generated a table with 4M random timestamps, truncated to both day and second values as follows:

test_db=> SELECT * INTO ts_test FROM 
                        (SELECT id, 
                                ts, 
                                date_trunc('day', ts) AS trunc_day, 
                                date_trunc('second', ts) AS trunc_s 
                         FROM (select generate_series(1, 4000000) AS id, 
                               now() - '1 year'::interval * round(random() * 1000) AS ts) AS sub) 
                         AS subq;
SELECT 4000000

test_db=> create index ix_day_trunc on ts_test (id, trunc_day);
CREATE INDEX
test_db=> create index ix_second_trunc on ts_test (id, trunc_s);
CREATE INDEX
test_db=> \d ts_test
           Table "public.ts_test"
  Column   |           Type           | Modifiers 
-----------+--------------------------+-----------
 id        | integer                  | 
 ts        | timestamp with time zone | 
 trunc_day | timestamp with time zone | 
 trunc_s   | timestamp with time zone | 
Indexes:
    "ix_day_trunc" btree (id, trunc_day)
    "ix_second_trunc" btree (id, trunc_s)

test_db=> SELECT pg_size_pretty(pg_relation_size('ix_day_trunc'));
          pg_size_pretty 
          ----------------
          120  MB
          (1 row)

test_db=> SELECT pg_size_pretty(pg_relation_size('ix_second_trunc'));
          pg_size_pretty 
          ----------------
          120 MB
          (1 row)