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=26435 loops=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.350 rows=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.065 rows=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(at)masterhost(dot)ru> writes:
>> Looks like when indexed search over NULL's added, planner wasn't learned right way use such index on "where something is 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