I have a table
foo(a1, a2, a3, a4, a5)
a1 is the primary key.
there is a non-clustering index on a5.
I have a simple query:
SELECT *
FROM foo
WHERE a5/100 = 20;
This query runs significantly slower. updating the statistics used in query planning did not help much.
Why could this be happening? What could I be doing wrong? I'm new to query optimization.
You are using an expression on the column in the WHERE predicate so it is not sargable(can`t use an index).
This is leaving aside the possible problem of cardinality,that is data distributions - if your WHERE comditions return more than around 40% of the row an index becomes useless.
EDIT
In an index you search for a value,if that value is the result of an expression the index cant be used.Also operator like : NOT, NOT IN,<> are also not-sargable because for an index search you need a clear value(s) so the optimizer can define some sort of fixed range. With your calculations on the fly the value constantly changes so you need to scan the whole table.
You can create an index on expressions instead of the base data. If you know you're always going to be dividing a5 by 100, you can do an index with:
CREATE INDEX ON foo ((a5/100));
The extra brackets are required.
This way, any query that has WHERE a5/100 = <something> will be able to take advantage of the index.
It won't help for WHERE a5/99 = <something> etc. though
Docs at https://www.postgresql.org/docs/current/static/indexes-expressional.html