How to add an index with WHERE-clause in Postgres

Question

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

You cannot index the most recent 90 days -- that is what the error means. Just index all the data. — Gordon Linoff
– Gordon Linoff, Commented Mar 1, 2016 at 13:17
Can you elaborate on what you are trying to do? Looks like a XY Problem — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Mar 1, 2016 at 13:19
line is a big table so I wanted to create index with daterange. When we are creating index, it will have virtual table with only 90 days records. So Querying ll be faster @JuanCarlosOropeza — Kathirvel Appusamy
– Kathirvel Appusamy, Commented Mar 1, 2016 at 13:24
error exception is raised because now() is not an immutable function, its by all means volatile. you cant use volatile function in any index. — LongBeard_Boldy
– LongBeard_Boldy, Commented Mar 1, 2016 at 13:25
Instead of trying to spoof partitioning, just go ahead and partition. Rename line to line_archive. Create a new line table with same structure and make it the parent of line_archive. Create a line_current table that inherits from line. Create a nightly process that moves data > 90 days old from line_current to line_archive. All new inserts need to go into line_current, but you can hide that from code with a before trigger on the line table. Updates and deletes will continue to work. You will still need the date index on both tables, but queries will be fast. — Jim Nasby
– Jim Nasby, Commented Mar 1, 2016 at 14:26

Nick Barnes · Accepted Answer · 2016-03-01 14:10:38Z

6

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.

answered Mar 1, 2016 at 14:10

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LongBeard_Boldy Over a year ago

I do object to the last statement, current_date will use index in that particular query

Nick Barnes Over a year ago

@LongBeard_Boldy: It won't; see this SQLFiddle

LongBeard_Boldy Over a year ago

Sorry, my bad. True, if you use partial index, query with non immutable functions in where clause wont use index. But if you will index whole column, planner will use that index.

Juan Carlos Oropeza · Accepted Answer · 2016-03-01 13:29:18Z

1

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.

edited Mar 1, 2016 at 13:29

answered Mar 1, 2016 at 13:25

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

3 Comments

Kathirvel Appusamy Over a year ago

Here , it will do select from complete table which I am not expecting as table is huge

Juan Carlos Oropeza Over a year ago

Nope, The index will filter the rows from you. If you add EXPLAIN ANALYZE as I just edit, you can see on the query plan how the index filter the data.

LongBeard_Boldy Over a year ago

is date field a timestamp or date field? in where clause you could use current_date function instead of now()

Collectives™ on Stack Overflow

How to add an index with WHERE-clause in Postgres

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related