Re: pgsql do not handle NULL constants in the view

Поиск
Список
Период
Сортировка
От Sergey Hripchenko
Тема Re: pgsql do not handle NULL constants in the view
Дата
Msg-id 99EB298E5D3A2C46BD744630B3FD093E9515D604B8@EXVMBX-1.intermedia-inc.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.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Zoltan Boszormenyi
Дата:
Сообщение: Re: Slow query with a lot of data
Следующее
От: Sergey Hripchenko
Дата:
Сообщение: pgsql do not handle NULL constants in the view