pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpgsql do not handle NULL constants in the view
4+ messages / 2 participants
[nested] [flat]
* pgsql do not handle NULL constants in the view
@ 2008-08-20 09:17 Sergey Hripchenko <shripchenko@intermedia.net>
2008-08-20 12:54 ` Re: pgsql do not handle NULL constants in the view Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Sergey Hripchenko @ 2008-08-20 09:17 UTC (permalink / raw)
To: 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)
________________________________
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pgsql do not handle NULL constants in the view
2008-08-20 09:17 pgsql do not handle NULL constants in the view Sergey Hripchenko <shripchenko@intermedia.net>
@ 2008-08-20 12:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-20 13:49 ` Re: pgsql do not handle NULL constants in the view Sergey Hripchenko <shripchenko@intermedia.net>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2008-08-20 12:54 UTC (permalink / raw)
To: Sergey Hripchenko <shripchenko@intermedia.net>; +Cc: pgsql-performance
Sergey Hripchenko <shripchenko@intermedia.net> writes:
> 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;
Try casting the NULL to integer (or whatever the datatype of the other
union arm is) explicitly.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pgsql do not handle NULL constants in the view
2008-08-20 09:17 pgsql do not handle NULL constants in the view Sergey Hripchenko <shripchenko@intermedia.net>
2008-08-20 12:54 ` Re: pgsql do not handle NULL constants in the view Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-08-20 13:49 ` Sergey Hripchenko <shripchenko@intermedia.net>
0 siblings, 0 replies; 4+ messages in thread
From: Sergey Hripchenko @ 2008-08-20 13:49 UTC (permalink / raw)
To: pgsql-performance
Thx it helps.
Shame on me %) I forgot that NULL itself has no type, and thought that each constant in the view are casted to the resulting type at the creation time.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, August 20, 2008 4:54 PM
To: Sergey Hripchenko
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] pgsql do not handle NULL constants in the view
Sergey Hripchenko <shripchenko@intermedia.net> writes:
> 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;
Try casting the NULL to integer (or whatever the datatype of the other
union arm is) explicitly.
regards, tom lane
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pgsql do not handle NULL constants in the view
@ 2008-08-20 09:19 Sergey Hripchenko <shripchenko@intermedia.net>
0 siblings, 0 replies; 4+ messages in thread
From: Sergey Hripchenko @ 2008-08-20 09:19 UTC (permalink / raw)
To: Sergey Hripchenko <shripchenko@intermedia.net>; pgsql-performance
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@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@ast-sql data]# cat /etc/redhat-release
Fedora release 7 (Moonshine)
[root@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@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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-08-20 13:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-08-20 09:17 pgsql do not handle NULL constants in the view Sergey Hripchenko <shripchenko@intermedia.net>
2008-08-20 12:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-08-20 13:49 ` Sergey Hripchenko <shripchenko@intermedia.net>
2008-08-20 09:19 Re: pgsql do not handle NULL constants in the view Sergey Hripchenko <shripchenko@intermedia.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox