How does index on function make query faster?

Question

I was trying to make my queries faster in POSTGRESQL. There is a item table with columns price and vat. I wanted to choose items with price include vat higher than, let's say, x.

SELECT * FROM item WHERE (price*(1+VAT/100.0))>x

EXPLAIN returned some cost - about 8000.

To make it faster, I've created a function price_vat(price,vat) which computes the same thing (price*(1+VAT/100.0)). When I replaced (price*(1+VAT/100.0)) by price_vat(price,vat), the execution cost slightly raised (about 9000).

So I've created an INDEX

CREATE INDEX price_vat_index on item (price_vat(price,vat));

Now, when I run the query EXPLAIN SELECT * FROM item WHERE price_vat(price,vat)>x it returns much lower cost (about 66% of original).

Does it computes the function on all rows and store them somewhere? If yes, what if I change VAT in some record? Do I have to create a trigger which creates the INDEX each time VAT or PRICE was changed?

Gordon Linoff · Accepted Answer · 2016-05-17 15:30:53Z

1

When you create an index on an expression, then Postgres needs to "materialize" the expression for all rows. That is, the expression is calculated for the rows.

The magic of relational databases is that the data remains accurate even when the data is updated. An update on a row requires recalculated the expression and adjusting the index.

This is why indexes on expressions (or computed columns in other databases) are a tad more complicated than other indexes. However, they use the same building blocks, and indexes on single columns also often have to be adjusted for updates.

answered May 17, 2016 at 15:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

How does index on function make query faster?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related