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.

up vote 6 down vote accepted

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.

  • Thanks, @Akash You know, it's strange just because I use one table in both of case. How can the planner know how rows it will be before the query executed? – folibis Feb 24 '15 at 5:46
  • @folibis the planner gathers stats (total no. of rows, value distribution, how fragemented the table is and so on.. ) when you analyze your tables, more on the doc here: postgresql.org/docs/9.0/static/sql-analyze.html – Akash Feb 24 '15 at 6:05
  • Ok, get it now. Thanks! – folibis Feb 24 '15 at 6:15

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.