sql - Add an index to a timestamp with time zone -

i want improve slow query, thinking add index, don't know index type better case.

select count(*) ct events dtt @ time zone 'america/santiago'    >= date(now() @ time zone 'america/santiago') + interval '1s'   

query plan:

"aggregate  (cost=128032.03..128032.04 rows=1 width=0) (actual time=3929.083..3929.083 rows=1 loops=1)" "  ->  seq scan on events  (cost=0.00..125937.68 rows=837742 width=0) (actual time=113.080..3926.972 rows=25849 loops=1)" "        filter: (timezone('america/santiago'::text, dtt) >= (date(timezone('america/santiago'::text, now())) + '00:00:01'::interval))" "        rows removed filter: 2487386" "planning time: 0.179 ms" "execution time: 3929.136 ms" 

• the query gets count of events of day.
• dtt timestamp time zone column.
• i'm using postgresql 9.4.

note: erwin advices query run little faster still think isn't fast enough.

"aggregate  (cost=119667.76..119667.77 rows=1 width=0) (actual time=3687.151..3687.152 rows=1 loops=1)" "  ->  seq scan on vehicle_events  (cost=0.00..119667.14 rows=250 width=0) (actual time=104.635..3687.068 rows=469 loops=1)" "        filter: (dtt >= timezone('america/santiago'::text, date_trunc('day'::text, timezone('america/santiago'::text, now()))))" "        rows removed filter: 2513337" "planning time: 0.164 ms" "execution time: 3687.204 ms" 

first, fix query make predicate sargable:

select count(*) ct   events  dtt >= date_trunc('day', now() @ time zone 'america/santiago')                                       @ time zone 'america/santiago' 

use column value , move calculations parameter.

that's right, after deriving local start of day, apply at time zone second time convert timestamp timestamptz again. details:

• ignoring timezones altogether in rails , postgresql

explanation step-by-step

1. now()
   .. postgres implementation sql standard current_timestamp. both 100 % equivalent, can use either. returns current point in time timestamptz - display of value takes time zone of current session consideration, that's irrelevant value.

2. now() at time zone 'america/santiago'
   .. computes local time given time zone. resulting data type timestamp. allow for:

3. date_trunc( now() @ time zone 'america/santiago' )
   .. truncates time component local start of day in 'america/santiago', independent of current time zone setting.

4. date_trunc('day', now() @ time zone 'america/santiago') at time zone 'america/santiago'
   .. feeding timestamp at time zone construct corresponding timestamptz value (utc internally) compare timestamptz value dtt to.

i removed + interval '1s', suspecting have been abusing convert date timestamp. use date_trunc() instead produce timestamp value.

now, plain (default) btree index on dtt do. of course, index used, if predicate selective enough.

create index events_dtt_idx on events (dtt); 

if important queries consider recent rows, partial index might more. details:

• get latest child per parent big table - query slow