StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Interesting question! According to my investigation they will be the same size.</p> <p>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 (<a href="http://www.postgresql.org/docs/9.1/static/datatype-datetime.html#DATATYPE-DATETIME-TABLE" rel="nofollow">8 bytes</a>), 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. </p> <p>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:</p> <pre><code>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) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload