Add an index to a timestamp with time zone

Question

I want to improve this slow query, I thinking to add an index, but I don't know what index type is better to my case.

SELECT COUNT(*) ct FROM events
WHERE dtt AT TIME ZONE 'America/Santiago'
   >= date(now() AT 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 by Filter: 2487386"
"Planning time: 0.179 ms"
"Execution time: 3929.136 ms"

• The query gets the count of events of the day.
• dtt is a timestamp with time zone column.
• I'm using Postgresql 9.4.

Note: With the Erwin advices the query run a little faster but still I 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 by Filter: 2513337"
"Planning time: 0.164 ms"
"Execution time: 3687.204 ms"

Answer 1

First, fix your query to make the predicate sargable:

SELECT count(*) AS ct
FROM   events
WHERE  dtt >= date_trunc('day', now() AT TIME ZONE 'America/Santiago')
                                      AT TIME ZONE 'America/Santiago'

Use the column value as is and move all calculations to the parameter.

That's right, after deriving the local start of the day, apply AT TIME ZONE a second time to convert the timestamp back to timestamptz again. Details:

• Ignoring timezones altogether in Rails and PostgreSQL

Explanation step-by-step

1. now()
   .. is the Postgres implementation for the SQL standard CURRENT_TIMESTAMP. Both are 100 % equivalent, you can use either. It returns the current point in time as timestamptz - the display of the value takes the time zone of the current session into consideration, but that's irrelevant for the value.

2. now() AT TIME ZONE 'America/Santiago'
   .. computes the local time for the given time zone. The resulting data type is timestamp. We do this to allow for:

3. date_trunc( now() AT TIME ZONE 'America/Santiago' )
   .. truncates the time component to get the local start of the day in 'America/Santiago', independent of the current time zone setting.

4. date_trunc('day', now() AT TIME ZONE 'America/Santiago') AT TIME ZONE 'America/Santiago'
   .. feeding the timestamp to the AT TIME ZONE construct we get the corresponding timestamptz value (UTC internally) to compare the timestamptz value dtt to.

I removed the + interval '1s', suspecting you have just been abusing that to convert the date to timestamp. Use date_trunc() instead to produce a timestamp value.

Now, a plain (default) btree index on dtt will do. Of course, the index will only be used, if the predicate is selective enough.

CREATE INDEX events_dtt_idx ON events (dtt);

If your important queries only consider recent rows, a partial index might help some more. Details:

• Get latest child per parent from big table - query is too slow