How can I utilize a partial index for the calculated filter condition in a where clause?

Let’s say I have this simple query:

EXPLAIN ANALYZE
SELECT
    COUNT(*)
FROM
    audiences a
WHERE
    a.created_at >= (current_date - INTERVAL '5 days');

This is a 1GB+ table with a partial index on created_at column. When I run this query it does sequential scan and does not utilise my index which obviously takes much time:

Aggregate  (cost=345853.43..345853.44 rows=1 width=8) (actual time=27126.426..27126.426 rows=1 loops=1)
  ->  Seq Scan on audiences a  (cost=0.00..345840.46 rows=5188 width=0) (actual time=97.564..27124.317 rows=8029 loops=1)
        Filter: (created_at >= (('now'::cstring)::date - '5 days'::interval))
        Rows Removed by Filter: 2215612
Planning time: 0.131 ms
Execution time: 27126.458 ms

On the other hand if I’d have a “hardcoded” (or pre-calculated) value like this:

EXPLAIN ANALYZE
SELECT
    COUNT(*)
FROM
    audiences a
WHERE
    a.created_at >= TIMESTAMP '2020-10-16 00:00:00';

It would utilise an index on created_at:

Aggregate  (cost=253.18..253.19 rows=1 width=8) (actual time=1014.655..1014.655 rows=1 loops=1)
  ->  Index Only Scan using index_audiences_on_created_at on audiences a  (cost=0.29..240.21 rows=5188 width=0) (actual time=1.308..1011.071 rows=8029 loops=1)
        Index Cond: (created_at >= '2020-10-16 00:00:00'::timestamp without time zone)
        Heap Fetches: 6185
Planning time: 1.878 ms
Execution time: 1014.716 ms

If I could I’d just use an ORM and generate a query with the right value but I can’t. Is there a way I can maybe pre-calculate this timestamp and use it in a WHERE clause via plain SQL?

Adding a little bit of tech info of my setup.

PostgreSQL version: 9.6.11

created_at column type is: timestamp

index: "index_audiences_on_created_at" btree (created_at) WHERE created_at > '2020-10-01 00:00:00'::timestamp without time zone

Answer

This is not the exact answer. But can do with specific situation

As you have the predicate (created_at > '2020-10-01 00:00:00'::timestamp without time zone) , if the filtering condition is greater than the predicate condition. Then you can prepend the condition in where

EXPLAIN ANALYZE
SELECT
    COUNT(*)
FROM
    audiences a
WHERE
    a.created_at >= TIMESTAMP '2020-10-16 00:00:00'
    and
    a.created_at >= (current_date - INTERVAL '5 days');

Note: may be instead of TIMESTAMP , you have to put TIMESTAMP without time zone or TIMESTAMP with time zone. Depends on column type

Advertisement

Answer