sql - PostgreSQL timestamp index not used when cast to date -

this query:

select     i::date day,     (select count(*) genericevent event = 'chat_message' , eventdate::date = i::date , extra1 = 'public') message_public_total,     (select count(*) genericevent event = 'chat_message' , eventdate::date = i::date , extra1 = 'public' , extra2 = 'clean') message_public_clean generate_series('2013-08-01', '2013-08-27', interval '1 day') 

i have index human consider usable query (in fact should result in index-only scan):

create index idx__genericevent__event__extra1__date   on genericevent   using btree   (event collate pg_catalog."default", extra1 collate pg_catalog."default", eventdate); 

however, explained, postgresql doesn't deem so. uses event , extra1 index, not eventdate (see index cond lines):

"function scan on generate_series  (cost=0.00..145219698.17 rows=1000 width=8)" "  subplan 1" "    ->  aggregate  (cost=72274.87..72274.88 rows=1 width=0)" "          ->  bitmap heap scan on genericevent  (cost=11367.74..72271.51 rows=1345 width=0)" "                recheck cond: (((event)::text = 'chat_message'::text) , ((extra1)::text = 'public'::text))" "                filter: ((eventdate)::date = (i.i)::date)" "                ->  bitmap index scan on idx__genericevent__event__extra1__date  (cost=0.00..11367.40 rows=269012 width=0)" "                      index cond: (((event)::text = 'chat_message'::text) , ((extra1)::text = 'public'::text))" "  subplan 2" "    ->  aggregate  (cost=72944.79..72944.80 rows=1 width=0)" "          ->  bitmap heap scan on genericevent  (cost=11367.50..72943.80 rows=396 width=0)" "                recheck cond: (((event)::text = 'chat_message'::text) , ((extra1)::text = 'public'::text))" "                filter: (((extra2)::text = 'clean'::text) , ((eventdate)::date = (i.i)::date))" "                ->  bitmap index scan on idx__genericevent__event__extra1__date  (cost=0.00..11367.40 rows=269012 width=0)" "                      index cond: (((event)::text = 'chat_message'::text) , ((extra1)::text = 'public'::text))" 

i think may have eventdate::date cast. how can change query or index improve performance?

for completeness, here's table:

create table genericevent (   id bigint not null,   eventdate timestamp time zone not null,   event character varying(50) not null,   extra1 character varying(100),   extra2 character varying(100),   constraint genericevent_pkey primary key (id) ) 

you need use timestamps work, rather dates.

on paper, change index expression date truncated specified date. won't work if time stamp has time zone, since it's volatile due theoretical potential server's timezone change.

in practice, you'd need change equality clause equivalent inequality, e.g. like:

eventdate >= , eventdate < + interval '1 day' 

but before proceeding rewriting query, note add appropriate clauses clodoaldo neto's query:

select     i::date day,     count(*) message_public_total,     count(extra2 = 'clean' or null) message_public_clean     genericevent     right join     generate_series(         '2013-08-01', '2013-08-27', interval '1 day'     ) on eventdate::date = i::date     event = 'chat_message'     , extra1 = 'public'     , eventdate >= '2013-08-01'     , eventdate < '2013-08-27' + interval '1 day' group 1 

or:

select     i::date day,     count(*) message_public_total,     count(extra2 = 'clean' or null) message_public_clean     genericevent     right join     generate_series(         '2013-08-01', '2013-08-27', interval '1 day'     ) on eventdate >= , eventdate < + interval '1 day'     event = 'chat_message'     , extra1 = 'public' --    , eventdate >= '2013-08-01' --    , eventdate < '2013-08-27' + interval '1 day' group 1