Re: Indexes on NULL's and order by ... limit N queries

Sorry with all my respect to you, you look like wrong.
Here example:

With NULL's:
mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id IS NULL ORDER BY pos LIMIT
5;
                                                                   QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=1503.75..1503.76 rows=5 width=28) (actual time=93.334..93.353 rows=5 loops=1)
    ->  Sort  (cost=1503.75..1569.84 rows=26435 width=28) (actual time=93.329..93.335 rows=5 loops=1)
          Sort Key: pos
          Sort Method:  top-N heapsort  Memory: 25kB
          ->  Bitmap Heap Scan on cluster_weight  (cost=314.32..1064.67 rows=26435 width=28) (actual time=7.519..48.678
rows=26435loops=1) 
                Recheck Cond: (rubric_id IS NULL)
                ->  Bitmap Index Scan on cluster_weight_2  (cost=0.00..307.72 rows=26435 width=0) (actual
time=7.350..7.350rows=26435 loops=1) 
                      Index Cond: (rubric_id IS NULL)
  Total runtime: 93.433 ms
(9 rows)

Now lets change NULL's to -1
mboguk_billing=# UPDATE cluster_weight set rubric_id=-1 where rubric_id IS NULL;
UPDATE 26435

And ANALYZE
mboguk_billing=# ANALYZE cluster_weight;
ANALYZE

And try same query with -1 instead of NULL:
mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id=-1 ORDER BY pos LIMIT 5;
                                                                   QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..0.25 rows=5 width=28) (actual time=0.056..0.080 rows=5 loops=1)
    ->  Index Scan using cluster_weight_2 on cluster_weight  (cost=0.00..1334.41 rows=26435 width=28) (actual
time=0.053..0.065rows=5 loops=1) 
          Index Cond: (rubric_id = (-1))
  Total runtime: 0.133 ms
(4 rows)


And plan become normal. So issue not with too many NULL's in my dataset.


--
SY, Maxim Boguk

Tom Lane wrote:
> Maxim Boguk <mboguk@masterhost.ru> writes:
>> Looks like when indexed search over NULL's added, planner wasn't learned right way use such index on  "where
somethingis NULL order by ... limit ..."  
>> queries.
>
> There's nothing wrong with the plan; you've just got too many NULLs to
> make it worth using the index for that.
>
>             regards, tom lane