RE: IS NOT DISTINCT FROM statement

От: Artur Zając
Тема: RE: IS NOT DISTINCT FROM statement
Дата: ,
Msg-id: 014101d4d5aa$084e8ff0$18ebafd0$@ang.com.pl
(см: обсуждение, исходный текст)
Ответ на: Re: IS NOT DISTINCT FROM statement  (David Rowley)
Ответы: Re: IS NOT DISTINCT FROM statement  (David Rowley)
Список: pgsql-performance

Скрыть дерево обсуждения

IS NOT DISTINCT FROM statement  (Artur Zając, )
 Re: IS NOT DISTINCT FROM statement  (Laurenz Albe, )
 Re: IS NOT DISTINCT FROM statement  (David Rowley, )
  RE: IS NOT DISTINCT FROM statement  (Artur Zając, )
   Re: IS NOT DISTINCT FROM statement  (David Rowley, )
    Re: IS NOT DISTINCT FROM statement  (Tom Lane, )

> In short, probably possible, but why not just write an equality clause, if you know NULLs are not possible?

In fact I construct query like this (usually in pl/pgsql).

SELECT column FROM table WHERE column1 IS NOT DISTINCT FROM $1 AND column2 IS NOT DISTINCT FROM $2;

"IS NOT DISTINCT FROM" statement simplifies the query ($1 OR $2 may be null, col1 and col2 has indexes).

I made some workaround. I made function:

CREATE OR REPLACE FUNCTION smarteq(v1 int,v2 INT) RETURNS BOOL AS
$BODY$
    SELECT (CASE WHEN v2 IS NULL THEN (v1 IS NULL) ELSE v1=v2 END);
$BODY$ LANGUAGE 'sql' IMMUTABLE PARALLEL SAFE;


And then

explain analyze select id from sometable where smarteq(id1,21580);
                                                            QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------
 Index Scan using sometable_index1 on sometable  (cost=0.43..8.45 rows=1 width=4) (actual time=0.085..0.086 rows=1
loops=1)
   Index Cond: (id1 = 21580)
 Planning time: 0.223 ms
 Execution time: 0.117 ms
(4 rows)

explain analyze select id from sometable where smarteq(id1,NULL);
                                                                  QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on sometable  (cost=19338.59..57045.02 rows=882343 width=4) (actual time=116.236..306.304 rows=881657
loops=1)
   Recheck Cond: (id1 IS NULL)
   Heap Blocks: exact=9581
   ->  Bitmap Index Scan on sometable_index1  (cost=0.00..19118.00 rows=882343 width=0) (actual time=114.209..114.209
rows=892552loops=1) 
         Index Cond: (id1 IS NULL)
 Planning time: 0.135 ms
 Execution time: 339.229 ms

It looks like it works, but I must check if it will still works in plpgsql (I expect some problems if query is
prepared).

Artur Zajac





В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: IS NOT DISTINCT FROM statement
От: Daulat Ram
Дата:
Сообщение: Shared_buffers