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 [EMAIL PROTECTED]
asco GmbH http://www.asco.de
Mittelweg 7 Tel 0531/3906-127
38106 BraunschweigFax 0531/3906-400
---(end of broadcast)---
TIP 6: explain analyze is your friend
