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 =
(SELECT max(module_ident) FROM modules
WHERE m.moduleid = moduleid GROUP BY moduleid);
repository=# explain analyze select * from current_modules where name ~ 'Fourier';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on 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) (actual time=0.41..0.41 rows=1
loops=4534)
-> Group (cost=0.00..25.55 rows=6 width=13) (actual time=0.08..0.37
rows=10 loops=4534)
-> Index Scan using moduleid_idx on modules (cost=0.00..25.54
rows=6 width=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 Scan on 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.35 rows=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.22 rows=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 Scan on 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) (actual time=0.41..0.41 rows=1
loops=4534)
-> Group (cost=0.00..25.55 rows=6 width=13) (actual time=0.08..0.37
rows=10 loops=4534)
-> Index Scan using moduleid_idx on modules (cost=0.00..25.54
rows=6 width=13) (actual time=0.06..0.27 rows=10 loops=4534)
Index Cond: ($0 = moduleid)
Total runtime: 1959.84 msec
(8 rows)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
