sql query runs slower than expected

Question

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.

`WHERE a5/100 = 20;` --> try `WHERE a5 = 2000;` instead, and make sure that there is an index created on `a5` column. — krokodilko, Apr 21 '17 at 07:29
Please **[EDIT]** your question and add the execution plan generated using **`explain (analyze, verbose)`**. [**Formatted text**](http://stackoverflow.com/help/formatting) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) — , Apr 21 '17 at 07:31

Mihai · Accepted Answer · 2017-04-21T08:02:14.680

0

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.

edited Apr 21 '17 at 08:02

answered Apr 21 '17 at 07:28

Mihai

26,325
7
66
81

Thanks for your response. Could you please elaborate a bit. This seems to be pretty condensed. – blabla Apr 21 '17 at 07:33
1

It's not entirely true that `<>` or `NOT IN` can not use an index. A partial index defined with `where ... <> ...` might be usable for such a condition – Apr 21 '17 at 08:08
I have an additional followup question: what happens if a non-clustering index is created on a column that has some missing/null values? – blabla Apr 21 '17 at 19:49

score 0 · Answer 2 · answered Apr 21 '17 at 09:11

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

sql query runs slower than expected

2 Answers2