I have some query:
EXPLAIN ANALYZE select id from sometable where fkey IS NOT DISTINCT FROM 21580;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Gather (cost=10.00..39465.11 rows=1 width=4) (actual time=0.512..129.625 rows=1 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on sometable (cost=0.00..39455.01 rows=1 width=4) (actual time=77.995..103.806 rows=0 loops=5)
Filter: (NOT (fkey IS DISTINCT FROM 21580))
Rows Removed by Filter: 675238
Planning time: 0.101 ms
Execution time: 148.517 ms
Other Query:
EXPLAIN ANALYZE select id from table where fkey=21580;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Index Scan using sometable_index1 on sometable (cost=0.43..8.45 rows=1 width=4) (actual time=0.075..0.076 rows=1 loops=1)
Index Cond: (fkey = 21580)
Planning time: 0.117 ms
Execution time: 0.101 ms
(4 rows)
There is unique index on sometable(fkey);
Is there any reason that „NOT DISTINCT FROM” can’t be autotransformed to „=” when value on right side of expression is not NULL or is this any way to use index with „IS NOT DISTINCT FROM” statement?
Artur Zajac