Skip site navigation (1) Skip section navigation (2)

Inherited Table Query Planning (fwd)

From: Jens-Wolfhard Schicke <ml+pgsql-performance(at)asco(dot)de>
To: pgsql-performance(at)postgresql(dot)org
Subject: Inherited Table Query Planning (fwd)
Date: 2005-07-27 07:56:12
Message-ID: 66F42056CEA99778F918C7C9@[192.168.1.72] (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-performance
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(dot)schicke(at)asco(dot)de
asco GmbH		      http://www.asco.de
Mittelweg 7		      Tel 0531/3906-127
38106 Braunschweig	      Fax 0531/3906-400

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2005-07-27 14:09:08
Subject: Re: Inherited Table Query Planning (fwd)
Previous:From: Dawid KuroczkoDate: 2005-07-27 07:28:00
Subject: Re: [IMPORTANT] - My application performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group