CODEWITHSUNDEEP
postgresqlindexingwhere-clause
Kathirvel Appusamy
Kathirvel Appusamy

Reputation: 227

How to add an index with WHERE-clause in Postgres

I would like to add an index with a WHERE clause in Postgres. I used the following query to do that:

create index concurrently em_openorder_idx on line (m_product_id, org_id, date) where date >= now() - 90

But I am getting the following error:

functions in index predicate must be marked IMMUTABLE

Upvotes: 6

Views: 16634

Answers (2)

Nick Barnes
Nick Barnes

Reputation: 21316

The expression in the WHERE clause must be immutable, i.e. for a given set of arguments, it must return the same value every time you call it. now() clearly doesn't qualify.

You can index the last 90 days worth of data like this:

create index concurrently em_openorder_idx on line (m_product_id,org_id,date) 
where date>='now'::date-90

However, if you go back and look at the index definition, you will see that it has been transformed into a constant expression:

... WHERE date >= ('2016-03-02'::date - 90);

In other words, this 90-day window will not automatically move forwards over time; you will need to periodically drop and recreate this index yourself.

Another thing to note is that your queries can only use this index if they are comparing date with an immutable expression. For example, the index will be used here:

SELECT * FROM line WHERE date = '2016-03-02';

...but cannot be used here:

SELECT * FROM line WHERE date = CURRENT_DATE;

As an aside, if you're on Postgres 9.5, this table might be a good candidate for a BRIN index.

Upvotes: 6

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

Just create the index

create index concurrently em_openorder_idx on line (m_product_id,org_id,date)

I gues you want realize a query similar to this

EXPLAIN ANALYZE
SELECT *
FROM line
WHERE m_product_id = @id
  AND date>=now()-90

This will use the index and should be very fast.

Upvotes: 1

Related Questions