Is there a way to make the query planner consider pulling inner appends
outside joins?
Example:
natural_person inherits from person (obviously)
admpostgres3=# explain analyze select u.name, p.name from users u, person p
where p.user_id = u.id and u.name = 's_ohl';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=8.01..3350.14 rows=3 width=36) (actual
time=107.391..343.657 rows=10 loops=1)
Hash Cond: ("outer".user_id = "inner".id)
-> Append (cost=0.00..2461.34 rows=117434 width=20) (actual
time=0.007..264.910 rows=117434 loops=1)
-> Seq Scan on person p (cost=0.00..575.06 rows=31606 width=20)
(actual time=0.005..38.911 rows=31606 loops=1)
-> Seq Scan on natural_person p (cost=0.00..1886.28 rows=85828
width=19) (actual time=0.003..104.338 rows=85828 loops=1)
-> Hash (cost=8.01..8.01 rows=2 width=24) (actual time=0.096..0.096
rows=0 loops=1)
-> Index Scan using users_name_idx on users u (cost=0.00..8.01
rows=2 width=24) (actual time=0.041..0.081 rows=10 loops=1)
Index Cond: ((name)::text = 's_ohl'::text)
Total runtime: 343.786 ms
(9 rows)
admpostgres3=# explain analyze select u.name, p.name from users u, only
person p where p.user_id = u.id and u.name = 's_ohl' union all select
u.name, p.name from users u, only natural_person p where p.user_id = u.id
and u.name = 's_ohl';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..28.19 rows=3 width=28) (actual time=0.197..0.366
rows=10 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..14.12 rows=1 width=28)
(actual time=0.159..0.159 rows=0 loops=1)
-> Nested Loop (cost=0.00..14.11 rows=1 width=28) (actual
time=0.157..0.157 rows=0 loops=1)
-> Index Scan using users_name_idx on users u
(cost=0.00..8.01 rows=2 width=24) (actual time=0.039..0.075 rows=10 loops=1)
Index Cond: ((name)::text = 's_ohl'::text)
-> Index Scan using person_user_idx on person p
(cost=0.00..3.03 rows=2 width=8) (actual time=0.006..0.006 rows=0 loops=10)
Index Cond: (p.user_id = "outer".id)
-> Subquery Scan "*SELECT* 2" (cost=0.00..14.08 rows=2 width=28)
(actual time=0.036..0.193 rows=10 loops=1)
-> Nested Loop (cost=0.00..14.06 rows=2 width=28) (actual
time=0.033..0.171 rows=10 loops=1)
-> Index Scan using users_name_idx on users u
(cost=0.00..8.01 rows=2 width=24) (actual time=0.018..0.049 rows=10 loops=1)
Index Cond: ((name)::text = 's_ohl'::text)
-> Index Scan using natural_person_user_idx on
natural_person p (cost=0.00..3.01 rows=1 width=8) (actual
time=0.006..0.007 rows=1 loops=10)
Index Cond: (p.user_id = "outer".id)
Total runtime: 0.475 ms
(14 rows)
Mit freundlichem Gruß
Jens Schicke
--
Jens Schicke j.schicke@asco.de
asco GmbH http://www.asco.de
Mittelweg 7 Tel 0531/3906-127
38106 Braunschweig Fax 0531/3906-400
