Skip to content
Advertisement

Optimize BETWEEN date statement

I need help optimizing a Postgres query which uses the BETWEEN clause with a timestamp field.

I have 2 tables:

containing about 3394 rows

containing about 4000000 rows

There are btree indexes on both PKs id_one and id_two, on the FK id_one and cut_time.

I want to perform a query like:

This query retrieves about 1.700.000 rows in about 7 seconds.

Below the explain analyze report is reported:

The index on the timestamp column isn’t used. How to optimize this query?

Advertisement

Answer

The query executes in less than one second. The other 6+ seconds are spent on traffic between server and client.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement