Index Scans on PostgreSQL with Prepared Statements

I wanted to share a simple way to improve performance of a query running on PostgreSQL.

Let’s say you have a table containing millions of rows that has a time field with the UNIX epoch (bigint). Let’s also say you have an index on this field and you want to select all records that have time greater than a specific value.

If you run

select * from t where time > 1498652990

you’ll get all records starting Jan 1st 2017. The query will also be quite efficient as an index only scan will be performed. However, let’s say you want to keep this more flexible and decide to write

select * from t where time > extract(epoch from '2017-01-01'::date)

In this case, an index only scan will not be performed and the query will not be as efficient. Same if you were to compare time with the return from another function or another table’s field when doing a table join.

The solution here is to use prepared statements:

prepare my_statement (bigint) as (select * from t where time > $1);
execute my_statement(extract(epoch from '2017-01-01'::date));

This way the where clause in the select statement is evaluated against a constant and the index only scan is performed. Keep in mind that you cannot use subqueries when executing a prepared statement, so you might need to define a function and then run

execute my_statement(my_function());
One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.