Re: pgsql do not handle NULL constants in the view

Forgot to add

asteriskpilot=> SELECT version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.9 on i386-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070925 (Red Hat 4.1.2-27)
(1 row)

asteriskpilot=> \q
[root(at)ast-sql data]# uname -a
Linux ast-sql.intermedia.net 2.6.23.1-21.fc7 #1 SMP Thu Nov 1 21:09:24 EDT 2007 i686 i686 i386 GNU/Linux
[root(at)ast-sql data]# cat /etc/redhat-release
Fedora release 7 (Moonshine)
[root(at)ast-sql data]# rpm -qa | grep postgres
postgresql-8.2.9-1.fc7
postgresql-libs-8.2.9-1.fc7
postgresql-server-8.2.9-1.fc7
postgresql-contrib-8.2.9-1.fc7
postgresql-devel-8.2.9-1.fc7

________________________________
From: Sergey Hripchenko
Sent: Wednesday, August 20, 2008 1:17 PM
To: 'pgsql-performance(at)postgresql(dot)org'
Subject: pgsql do not handle NULL constants in the view

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)

________________________________
This message (including attachments) is private and confidential. If you have received this message in error, please notify us and remove it from your system.