|
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. |
