PostgreSQL: Create an index on timestamp::DATE [duplicate]

Question

I am creating a summary table that sums up all events in a given day.

INSERT INTO graph_6(
  day,
  event_type,
  (SELECT COUNT(*) FROM event e 
                  WHERE event_type = e.event_type 
                  AND creation_time::DATE = sq.day)
FROM event_type
CROSS JOIN
    (SELECT generate_series(
                (SELECT '2014-01-01'::DATE),
                (SELECT '2014-01-02'::DATE),
                '1 day') as day) sq;

The creation_time column is indexed:

CREATE INDEX event_creation_time_date_idx ON event USING BTREE(creation_time);

However, the query runs a pretty long time even when only querying two days of data with a handful of events (January 1-2 2014).

The EXPLAIN on the query is pretty grim - it runs a sequential scan on the event table, not utilizing the index at all:

->  Seq Scan on event e_1  (cost=0.00..12557.39 rows=531 width=38)
Filter: ... AND ((creation_time)::date = (generate_series(($12)::timestamp with time zone, ($13)::timestamp with time zone, '1 day'::interval))))

I assume this is because we compare a casted value - creation_time::DATE, not creation_time. I have tried indexing the cast:

CREATE INDEX event_creation_time_date_idx ON event USING BTREE(creation_time::DATE);

But got an error:

ERROR: syntax error at or near "::"

Is there a way to utilize PostgreSQL indices on a timezone column casted to DATE?

Am I losing JOIN it due to too much SQL JOIN? This is a JOIN duplicate of a JOIN question I have asked less than a month ago. A JOIN vacation and a JOIN good JOIN book JOIN JOIN JOIN! — Adam Matan
– Adam Matan, Commented Jan 11, 2016 at 15:14

klin · Accepted Answer · 2016-01-11 14:58:52Z

66

An expression in an index declaration should be enclosed in additional brackets, try:

CREATE INDEX event_creation_time_date_idx ON event ((creation_time::DATE));

answered Jan 11, 2016 at 14:58

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jap1968 Over a year ago

That will only work for timestamp without timezone. Otherwise you will get this error: ERROR: functions in index expression must be marked IMMUTABLE

thaddeusmt Over a year ago

You can get around the timezone issue (sort of) if you pick a timezone and convert to it both in your index and your queries: creation_time AT TIME ZONE 'UTC'

CaKa Over a year ago

If you are using the solution of @thaddeusmt, just make sure to also use that syntax (cast to utc) in your queries - otherwise it wont use the index. Thanks for the trick. ;)

ssi-anik Over a year ago

Creating an index works fine with thaddeusmt's suggestion. That was half of the solution for me. When querying data, the other half of the solution was found here. gist.github.com/cobusc/5875282. Commenting as someone else MAY need the suggestion too.

Collectives™ on Stack Overflow

PostgreSQL: Create an index on timestamp::DATE [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related