mardi 24 février 2015

Index for numeric field is not used


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.





Aucun commentaire:

Enregistrer un commentaire