Search code examples
sqlpostgresqlquery-planner

sql query runs slower than expected

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.

Solution

  • 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.