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

Alvaro Herrera wrote:
> Maxim Boguk wrote:
>> Sorry with all my respect to you, you look like wrong.
>
> The difference is that the plan with -1 does not need to sort the
> output, because it comes sorted out of the index; so the execution can
> be stopped as soon as 5 tuples have come out.  With NULL, that can't be
> done.

But why? NULL's have some special representation in index which don't work same as normal values?
Eg output with rubric_id is NULL dont come sorted from index?

Really my tests show same behavior of -1 and NULL values:

mboguk_billing=# SELECT pos from cluster_weight where rubric_id=-1 limit 20;
  pos
-----
   20
   20
   25
   40
   40
   50
   60
   60
   80
   80
  100
  120
  140
  160
  180
  200
  220
  240
  260
  280
(20 rows)

mboguk_billing=# UPDATE cluster_weight set rubric_id=NULL where rubric_id=-1;
UPDATE 26435
mboguk_billing=# ANALYZE cluster_weight;
ANALYZE
mboguk_billing=# SELECT pos from cluster_weight where rubric_id is NULL limit 20;
  pos
-----
   20
   20
   25
   40
   40
   50
   60
   60
   80
   80
  100
  120
  140
  160
  180
  200
  220
  240
  260
  280
(20 rows)

Eg output with rubric_id is NULL come ordered be pos from index (rubric_id, pos)

(
Here is explains:

mboguk_billing=# EXPLAIN ANALYZE SELECT pos from cluster_weight where rubric_id is NULL limit 20;
                                                                   QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..0.99 rows=20 width=2) (actual time=0.050..0.144 rows=20 loops=1)
    ->  Index Scan using cluster_weight_2 on cluster_weight  (cost=0.00..1314.94 rows=26435 width=2) (actual
time=0.045..0.082rows=20 loops=1) 
          Index Cond: (rubric_id IS NULL)
  Total runtime: 0.214 ms
(4 rows)

mboguk_billing=# UPDATE cluster_weight set rubric_id=-1 where rubric_id IS NULL;
UPDATE 26435
mboguk_billing=# ANALYZE cluster_weight;
ANALYZE

mboguk_billing=# EXPLAIN ANALYZE SELECT pos from cluster_weight where rubric_id=-1 limit 20;
                                                                   QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..0.95 rows=20 width=2) (actual time=0.050..0.141 rows=20 loops=1)
    ->  Index Scan using cluster_weight_2 on cluster_weight  (cost=0.00..1259.05 rows=26435 width=2) (actual
time=0.045..0.081rows=20 loops=1) 
          Index Cond: (rubric_id = (-1))
  Total runtime: 0.214 ms
(4 rows)

Plans look same.
)

PS: REINDEX do not change situation.

--
SY Maxim Boguk