pgsql do not handle NULL constants in the view - Mailing list pgsql-performance

Hi all,

Looks like I found a bug with views optimization:

For example create a test view:

CREATE OR REPLACE VIEW bar AS

SELECT *

FROM (

    (

        SELECT calldate, duration, billsec, get_asterisk_cdr_caller_id(accountcode) AS caller_id

        FROM asterisk_cdr

    ) UNION ALL (

        SELECT start_time, get_interval_seconds(completed_time-start_time), get_interval_seconds(answered_time-start_time), NULL

        FROM asterisk_huntgroups_calls

    )

) AS foo;

And perform select on it:

EXPLAIN SELECT * FROM bar WHERE caller_id = 1007;

Theoretically second UNION statement shouldn’t be executed at all (because 1007 != NULL)… but postgres performs seq-scans on both UNION parts.

asteriskpilot=> EXPLAIN ANALYZE SELECT * FROM bar WHERE caller_id = 1007;

                                                                      QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------

 Subquery Scan foo  (cost=0.00..94509.49 rows=7303 width=28) (actual time=12249.473..14841.648 rows=25 loops=1)

   Filter: (caller_id = 1007)

   ->  Append  (cost=0.00..76252.26 rows=1460578 width=24) (actual time=0.065..13681.814 rows=1460405 loops=1)

         ->  Subquery Scan "*SELECT* 1"  (cost=0.00..57301.22 rows=1120410 width=20) (actual time=0.064..10427.353 rows=1120237 loops=1)

               ->  Seq Scan on asterisk_cdr  (cost=0.00..46097.12 rows=1120410 width=20) (actual time=0.059..8326.974 rows=1120237 loops=1)

         ->  Subquery Scan "*SELECT* 2"  (cost=0.00..18951.04 rows=340168 width=24) (actual time=0.034..1382.653 rows=340168 loops=1)

               ->  Seq Scan on asterisk_huntgroups_calls  (cost=0.00..15549.36 rows=340168 width=24) (actual time=0.031..863.529 rows=340168 loops=1)

 Total runtime: 14841.739 ms

(8 rows)

But if we wrap this NULL value into the _IMMUTABLE RETURNS NULL ON NULL INPUT_ function postgres handle this view properly

asteriskpilot=> EXPLAIN SELECT * FROM bar WHERE caller_id = 1007;

                                             QUERY PLAN

-----------------------------------------------------------------------------------------------------

 Append  (cost=20.21..15663.02 rows=1015 width=24)

   ->  Subquery Scan "*SELECT* 1"  (cost=20.21..3515.32 rows=1014 width=20)

         ->  Bitmap Heap Scan on asterisk_cdr  (cost=20.21..3505.18 rows=1014 width=20)

               Recheck Cond: (get_asterisk_cdr_caller_id(accountcode) = 1007)

               ->  Bitmap Index Scan on asterisk_cdr_caller_id  (cost=0.00..19.96 rows=1014 width=0)

                     Index Cond: (get_asterisk_cdr_caller_id(accountcode) = 1007)

   ->  Result  (cost=0.00..12147.69 rows=1 width=24)

         One-Time Filter: NULL::boolean

         ->  Seq Scan on asterisk_huntgroups_calls  (cost=0.00..12147.68 rows=1 width=24)