Postgresql – Index for numeric field is not used

indexpostgresql

I have PG server (9.0.4) installed on Debian x86.
In one of my tables I use numeric(6,1):

CREATE TABLE mytable(name text, numfield numeric(6,1))

Also index created for this field:

CREATE INDEX mytable_numfield_idx ON mytable USING btree (numfield);

Everything is going fine if I use this field with real numeric value. As I see the query uses the index:

EXPLAIN ANALYZE SELECT * FROM kodiall WHERE kodgo=123.0

Index Scan using mytable_numfield_idx on mytable (cost=0.00..8.27 rows=1
width=193) (actual time=0.085..0.087 rows=1 loops=1)

Index Cond: (numfield = 123.0)

Total runtime: 0.131 ms

But the index is ignored for some reason if I use 0.0 or NULL as condition value:

EXPLAIN ANALYZE SELECT * FROM kodiall WHERE numfield=0.0
--EXPLAIN ANALYZE SELECT * FROM kodiall WHERE numfield=NULL

Seq Scan on mytable (cost=0.00..57.80 rows=1080 width=193) (actual
time=0.033..1.853 rows=1088 loops=1)

Filter: (numfield = 0.0)

What wrong here? Is 0.0 not a numeric value?

In real query I use LEFT JOIN with numfield and values in joined table can be NULL.

Best Answer

While you EXPLAIN your query, in the first instance, you can see that the planner estimates the total row count to 1

Index Scan using mytable_numfield_idx on mytable (cost=0.00..8.27 **rows=1** width=193)

Using an index in this case is beneficial as there are hardly any rows matching your WHERE condition.

In the second case, there are around 1080 rows that meet your condition. Going through the index for each of these would be expensive when compared to a sequential scan. (More disk movements for reading index entries and then touching the actual table rows.)

Seq Scan on mytable (cost=0.00..57.80 **rows=1080** width=193)

You can still try forcing the planner to use the index for the second case by temporarily disabling a sequential scan. You can achieve that with:

BEGIN;
set enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM kodiall WHERE numfield=0.0
COMMIT;

The actual time taken by the above statement is likely to be more as compared to a sequential scan selected by the planner.

Note: Sequential scan is not always bad. If you are selecting a major chunk of your table it is faster to read the entire table and filter the rows rather than jumping between the pages of the index and the table itself.

Related Question