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
[EMAIL PROTECTED] 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
[EMAIL PROTECTED] data]# cat /etc/redhat-release
Fedora release 7 (Moonshine)
[EMAIL PROTECTED] 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@postgresql.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.
