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