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.082 rows=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.081 rows=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