CODEWITHSUNDEEP
sqlpostgresqlindexingcounttimezone
Goku
Goku

Reputation: 1840

Add an index to a timestamp with time zone

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"

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"

Upvotes: 8

Views: 9453

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658392

Adapt your query to make the predicate "sargable":

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

Use the column value as is and move all calculations to the parameter. The convenient variant of date_trunc() taking the time zone as 3rd parameter makes this a lot simpler - since Postgres 12. See:

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.

date_trunc('day', now(), 'America/Santiago')
.. gets the local start of the day in 'America/Santiago' as timestamptz, independent of the timezone setting of the current session.

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

Now, a plain B-tree 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:

Upvotes: 12

Related Questions