performance regression, 7.2.3 -> 7.3b5 w/ VIEW - Mailing list pgsql-hackers

Hey Hackers - 
I was testing beta5 and found a performance regression involving
application of constraints into a VIEW - I've got a view that is fairly
expensive, involving a subselet and an aggregate.  When the query is
rewritten in 7.2.3, the toplevel constraint is used to filter before
the subselect - in 7.3b5, it comes after.

For this query, the difference is 160 ms vs. 2 sec. Any reason for this
change?

Here's the view def., and explain analyzes for the view, and two hand
rewritten versions (since the explain analyze in 7.2.3 doesn't display
the filter parameters)

Ross

CREATE VIEW current_modules AS       SELECT * FROM modules m              WHERE module_ident =
(SELECTmax(module_ident) FROM modules                            WHERE m.moduleid = moduleid GROUP BY moduleid);
 

repository=# explain analyze select * from current_modules where name ~ 'Fourier';
                          QUERY PLAN                                                                  
 

----------------------------------------------------------------------------------------------------------------------------------------------Seq
Scanon modules m  (cost=0.00..116090.23 rows=1 width=135) (actual time=18.74..1968.01 rows=37 loops=1)  Filter:
((module_ident= (subplan)) AND (name ~ 'Fourier'::text))  SubPlan    ->  Aggregate  (cost=0.00..25.57 rows=1 width=13)
(actualtime=0.41..0.41 rows=1 loops=4534)          ->  Group  (cost=0.00..25.55 rows=6 width=13) (actual
time=0.08..0.37rows=10 loops=4534)                ->  Index Scan using moduleid_idx on modules  (cost=0.00..25.54
rows=6width=13) (actual time=0.06..0.27 rows=10 loops=4534)                      Index Cond: ($0 = moduleid)Total
runtime:1968.65 msec
 
(8 rows)

repository=# explain analyze select module_ident from modules m where m.name ~ 'Fourier' and m.module_ident = (SELECT
max(modules.module_ident)as max from modules where (m.moduleid=moduleid) group by modules.moduleid);
                                           QUERY PLAN                                                                 
 

--------------------------------------------------------------------------------------------------------------------------------------------Seq
Scanon modules m  (cost=0.00..116090.23 rows=1 width=4) (actual time=2.46..158.33 rows=37 loops=1)  Filter: ((name ~
'Fourier'::text)AND (module_ident = (subplan)))  SubPlan    ->  Aggregate  (cost=0.00..25.57 rows=1 width=13) (actual
time=0.35..0.35rows=1 loops=270)          ->  Group  (cost=0.00..25.55 rows=6 width=13) (actual time=0.07..0.31 rows=9
loops=270)               ->  Index Scan using moduleid_idx on modules  (cost=0.00..25.54 rows=6 width=13) (actual
time=0.06..0.22rows=9 loops=270)                      Index Cond: ($0 = moduleid)Total runtime: 158.81 msec
 
(8 rows)

repository=# explain analyze  select module_ident from modules m where m.module_ident = (SELECT
max(modules.module_ident)as max from modules where (m.moduleid=moduleid) group by modules.moduleid) and m.name ~
'Fourier';                                                                QUERY PLAN
                             
 

----------------------------------------------------------------------------------------------------------------------------------------------Seq
Scanon modules m  (cost=0.00..116090.23 rows=1 width=4) (actual time=18.66..1959.31 rows=37 loops=1)  Filter:
((module_ident= (subplan)) AND (name ~ 'Fourier'::text))  SubPlan    ->  Aggregate  (cost=0.00..25.57 rows=1 width=13)
(actualtime=0.41..0.41 rows=1 loops=4534)          ->  Group  (cost=0.00..25.55 rows=6 width=13) (actual
time=0.08..0.37rows=10 loops=4534)                ->  Index Scan using moduleid_idx on modules  (cost=0.00..25.54
rows=6width=13) (actual time=0.06..0.27 rows=10 loops=4534)                      Index Cond: ($0 = moduleid)Total
runtime:1959.84 msec
 
(8 rows)