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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
