postgresql datetrunc too slow 1

postgresql datetrunc too slow

CREATE INDEX ON test (date_trunc('day', updated_at));

/*If updated_at is a timestamp without time zone, that will work fine. 
For a timestamp with time zone, you'll have to specify a time zone, 
because otherwise the result will depend on the session time zone, 
which makes it unusable for an index:*/

CREATE INDEX ON test (date_trunc('day', updated_at AT TIME ZONE 'UTC'));

Here is what the above code is Doing:
1. The first line is creating a table called test.
2. The second line is creating a column called updated_at.
3. The third line is creating an index on the updated_at column.
4. The fourth line is creating an index on the updated_at column, but it’s truncating the time to the day.
5. The fifth line is creating an index on the updated_at column, but it’s truncating the time to the day and it’s using the UTC time zone.

Similar Posts