Re: Inheritance, unique keys and performance - Mailing list pgsql-performance

From Julian Scarfe
Subject Re: Inheritance, unique keys and performance
Date
Msg-id 01b601c855c4$8ed20a60$0600a8c0@Wilbur
Whole thread Raw
In response to Inheritance, unique keys and performance  ("Julian Scarfe" <julian@avbrief.com>)
Responses Re: Inheritance, unique keys and performance
List pgsql-performance
> Without the EXPLAIN ANALYZE output, nobody can say whether you have
> interpreted your performance problem correctly or not.

Fair enough, Tom.

  superclass = "geonode", subclass = "airport", expensive_function =
"gc_offroute".

For this test, some_table is also "airport".

There's also a coarse filter applied (using bounding boxes and an rtree
index) as well as the expensive_function.

(And before anyone suggests it, I know this looks geospatial, but I don't
think PostGIS does what I need.)

Thanks

Julian

----------------------------------------------

create temp table route_leg_tmp (
  route integer,
  seq_no integer,
  start_id integer,
  end_id integer
);
CREATE TABLE
insert into route_leg_tmp values (2,1,347428,347140);
INSERT 0 1
insert into route_leg_tmp values (2,2,347140,347540);
INSERT 0 1
insert into route_leg_tmp values (2,3,347540,347164);
INSERT 0 1
insert into route_leg_tmp values (2,4,347428,347140);
INSERT 0 1
insert into route_leg_tmp values (2,5,347140,347540);
INSERT 0 1
insert into route_leg_tmp values (2,6,347540,347164);
INSERT 0 1
analyze route_leg_tmp;
ANALYZE


test 1 subclass, scalar subquery
explain analyze
select airport.airport_id, airport.name, seq_no
  from airport, route_leg_tmp
  where box(airport.node,airport.node) && bounding_box(
    (select node from airport where geonode_id = start_id),
    (select node from airport where geonode_id = end_id),
    30.0)
  and gc_offroute(
    (select node from airport where geonode_id = start_id),
    (select node from airport where geonode_id = end_id),
    airport.node) < 30.0
and route = 2;
                                                                   QUERY
PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=9.25..8687.51 rows=165 width=24) (actual
time=41.585..57.670 rows=126 loops=1)
   Join Filter: (gc_offroute((subplan), (subplan), "inner".node) <
30::double precision)
   ->  Seq Scan on route_leg_tmp  (cost=0.00..1.07 rows=6 width=12) (actual
time=0.013..0.030 rows=6 loops=1)
         Filter: (route = 2)
   ->  Bitmap Heap Scan on airport  (cost=9.25..290.98 rows=83 width=36)
(actual time=0.122..0.285 rows=69 loops=6)
         Recheck Cond: (box(airport.node, airport.node) &&
bounding_box((subplan), (subplan), 30::double precision))
         ->  Bitmap Index Scan on geonode_node  (cost=0.00..9.25 rows=83
width=0) (actual time=0.110..0.110 rows=69 loops=6)
               Index Cond: (box(airport.node, airport.node) &&
bounding_box((subplan), (subplan), 30::double precision))
               SubPlan
                 ->  Index Scan using airport_geonode_id on airport
(cost=0.00..3.48 rows=1 width=16) (actual time=0.014..0.015 rows=1 loops=6)
                       Index Cond: (geonode_id = $1)
                 ->  Index Scan using airport_geonode_id on airport
(cost=0.00..3.48 rows=1 width=16) (actual time=0.020..0.022 rows=1 loops=6)
                       Index Cond: (geonode_id = $0)
         SubPlan
           ->  Index Scan using airport_geonode_id on airport
(cost=0.00..3.48 rows=1 width=16) (never executed)
                 Index Cond: (geonode_id = $1)
           ->  Index Scan using airport_geonode_id on airport
(cost=0.00..3.48 rows=1 width=16) (never executed)
                 Index Cond: (geonode_id = $0)
   SubPlan
     ->  Index Scan using airport_geonode_id on airport  (cost=0.00..3.48
rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=412)
           Index Cond: (geonode_id = $1)
     ->  Index Scan using airport_geonode_id on airport  (cost=0.00..3.48
rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=412)
           Index Cond: (geonode_id = $0)
 Total runtime: 58.227 ms
(24 rows)

test 2 subclass, join
explain analyze
select airport.airport_id, airport.name, seq_no
  from  airport, route_leg_tmp, airport g1, airport g2
  where box(airport.node,airport.node) && bounding_box(g1.node, g2.node,
30.0)
  and gc_offroute(g1.node, g2.node, airport.node) < 30.0
and route = 2
and start_id = g1.geonode_id
and end_id   = g2.geonode_id;
                                                                    QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=2.29..1758.30 rows=165 width=24) (actual
time=0.690..7.597 rows=126 loops=1)
   Join Filter: (gc_offroute("outer".node, "outer".node, "inner".node) <
30::double precision)
   ->  Nested Loop  (cost=0.00..42.97 rows=6 width=36) (actual
time=0.035..0.178 rows=6 loops=1)
         ->  Nested Loop  (cost=0.00..22.02 rows=6 width=24) (actual
time=0.024..0.106 rows=6 loops=1)
               ->  Seq Scan on route_leg_tmp  (cost=0.00..1.07 rows=6
width=12) (actual time=0.008..0.020 rows=6 loops=1)
                     Filter: (route = 2)
               ->  Index Scan using airport_geonode_id on airport g2
(cost=0.00..3.48 rows=1 width=20) (actual time=0.009..0.009 rows=1 loops=6)
                     Index Cond: ("outer".end_id = g2.geonode_id)
         ->  Index Scan using airport_geonode_id on airport g1
(cost=0.00..3.48 rows=1 width=20) (actual time=0.006..0.008 rows=1 loops=6)
               Index Cond: ("outer".start_id = g1.geonode_id)
   ->  Bitmap Heap Scan on airport  (cost=2.29..284.02 rows=83 width=36)
(actual time=0.087..0.171 rows=69 loops=6)
         Recheck Cond: (box(airport.node, airport.node) &&
bounding_box("outer".node, "outer".node, 30::double precision))
         ->  Bitmap Index Scan on geonode_node  (cost=0.00..2.29 rows=83
width=0) (actual time=0.078..0.078 rows=69 loops=6)
               Index Cond: (box(airport.node, airport.node) &&
bounding_box("outer".node, "outer".node, 30::double precision))
 Total runtime: 7.856 ms
(15 rows)

test 3 superclass, scalar subquery
explain analyze
select airport.airport_id, airport.name, seq_no
  from airport, route_leg_tmp
  where box(airport.node,airport.node) && bounding_box(
    (select node from geonode where geonode_id = start_id),
    (select node from geonode where geonode_id = end_id),
    30.0)
  and gc_offroute(
    (select node from geonode where geonode_id = start_id),
    (select node from geonode where geonode_id = end_id),
    airport.node) < 30.0
and route = 2;

                QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=61.46..60998.04 rows=165 width=24) (actual
time=1.455..59.031 rows=126 loops=1)
   Join Filter: (gc_offroute((subplan), (subplan), "inner".node) <
30::double precision)
   ->  Seq Scan on route_leg_tmp  (cost=0.00..1.07 rows=6 width=12) (actual
time=0.014..0.031 rows=6 loops=1)
         Filter: (route = 2)
   ->  Bitmap Heap Scan on airport  (cost=61.46..343.19 rows=83 width=36)
(actual time=0.089..0.220 rows=69 loops=6)
         Recheck Cond: (box(airport.node, airport.node) &&
bounding_box((subplan), (subplan), 30::double precision))
         ->  Bitmap Index Scan on geonode_node  (cost=0.00..61.46 rows=83
width=0) (actual time=0.079..0.079 rows=69 loops=6)
               Index Cond: (box(airport.node, airport.node) &&
bounding_box((subplan), (subplan), 30::double precision))
               SubPlan
                 ->  Result  (cost=0.00..29.58 rows=9 width=16) (actual
time=0.016..0.063 rows=1 loops=6)
                       ->  Append  (cost=0.00..29.58 rows=9 width=16)
(actual time=0.012..0.057 rows=1 loops=6)
                             ->  Index Scan using geonode_pkey on geonode
(cost=0.00..4.82 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=6)
                                   Index Cond: (geonode_id = $1)
                             ->  Index Scan using airport_geonode_id on
airport geonode  (cost=0.00..3.48 rows=1 width=16) (actual time=0.006..0.008
rows=1 loops=6)
                                   Index Cond: (geonode_id = $1)
                             ->  Index Scan using
airport_communications_geonode_id on airport_communications geonode
(cost=0.00..3.01 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=6)
                                   Index Cond: (geonode_id = $1)
                             ->  Index Scan using
airport_waypoint_geonode_id on airport_waypoint geonode  (cost=0.00..3.20
rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=6)
                                   Index Cond: (geonode_id = $1)
                             ->  Index Scan using
enroute_waypoint_geonode_id on enroute_waypoint geonode  (cost=0.00..3.01
rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=6)
                                   Index Cond: (geonode_id = $1)
                             ->  Index Scan using ils_navaid_geonode_id on
ils_navaid geonode  (cost=0.00..3.01 rows=1 width=16) (actual
time=0.004..0.004 rows=0 loops=6)
                                   Index Cond: (geonode_id = $1)
                             ->  Index Scan using ndb_navaid_geonode_id on
ndb_navaid geonode  (cost=0.00..3.01 rows=1 width=16) (actual
time=0.006..0.006 rows=0 loops=6)
                                   Index Cond: (geonode_id = $1)
                             ->  Index Scan using runway_geonode_id on
runway geonode  (cost=0.00..3.01 rows=1 width=16) (actual time=0.005..0.005
rows=0 loops=6)
                                   Index Cond: (geonode_id = $1)
                             ->  Index Scan using vhf_navaid_geonode_id on
vhf_navaid geonode  (cost=0.00..3.01 rows=1 width=16) (actual
time=0.004..0.004 rows=0 loops=6)
                                   Index Cond: (geonode_id = $1)
                 ->  Result  (cost=0.00..29.58 rows=9 width=16) (actual
time=0.028..0.136 rows=1 loops=6)
                       ->  Append  (cost=0.00..29.58 rows=9 width=16)
(actual time=0.024..0.130 rows=1 loops=6)
                             ->  Index Scan using geonode_pkey on geonode
(cost=0.00..4.82 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=6)
                                   Index Cond: (geonode_id = $0)
                             ->  Index Scan using airport_geonode_id on
airport geonode  (cost=0.00..3.48 rows=1 width=16) (actual time=0.009..0.010
rows=1 loops=6)
                                   Index Cond: (geonode_id = $0)
                             ->  Index Scan using
airport_communications_geonode_id on airport_communications geonode
(cost=0.00..3.01 rows=1 width=16) (actual time=0.014..0.014 rows=0 loops=6)
                                   Index Cond: (geonode_id = $0)
                             ->  Index Scan using
airport_waypoint_geonode_id on airport_waypoint geonode  (cost=0.00..3.20
rows=1 width=16) (actual time=0.014..0.014 rows=0 loops=6)
                                   Index Cond: (geonode_id = $0)
                             ->  Index Scan using
enroute_waypoint_geonode_id on enroute_waypoint geonode  (cost=0.00..3.01
rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=6)
                                   Index Cond: (geonode_id = $0)
                             ->  Index Scan using ils_navaid_geonode_id on
ils_navaid geonode  (cost=0.00..3.01 rows=1 width=16) (actual
time=0.012..0.012 rows=0 loops=6)
                                   Index Cond: (geonode_id = $0)
                             ->  Index Scan using ndb_navaid_geonode_id on
ndb_navaid geonode  (cost=0.00..3.01 rows=1 width=16) (actual
time=0.013..0.013 rows=0 loops=6)
                                   Index Cond: (geonode_id = $0)
                             ->  Index Scan using runway_geonode_id on
runway geonode  (cost=0.00..3.01 rows=1 width=16) (actual time=0.013..0.013
rows=0 loops=6)
                                   Index Cond: (geonode_id = $0)
                             ->  Index Scan using vhf_navaid_geonode_id on
vhf_navaid geonode  (cost=0.00..3.01 rows=1 width=16) (actual
time=0.012..0.012 rows=0 loops=6)
                                   Index Cond: (geonode_id = $0)
         SubPlan
           ->  Result  (cost=0.00..29.58 rows=9 width=16) (never executed)
                 ->  Append  (cost=0.00..29.58 rows=9 width=16) (never
executed)
                       ->  Index Scan using geonode_pkey on geonode
(cost=0.00..4.82 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $1)
                       ->  Index Scan using airport_geonode_id on airport
geonode  (cost=0.00..3.48 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $1)
                       ->  Index Scan using
airport_communications_geonode_id on airport_communications geonode
(cost=0.00..3.01 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $1)
                       ->  Index Scan using airport_waypoint_geonode_id on
airport_waypoint geonode  (cost=0.00..3.20 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $1)
                       ->  Index Scan using enroute_waypoint_geonode_id on
enroute_waypoint geonode  (cost=0.00..3.01 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $1)
                       ->  Index Scan using ils_navaid_geonode_id on
ils_navaid geonode  (cost=0.00..3.01 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $1)
                       ->  Index Scan using ndb_navaid_geonode_id on
ndb_navaid geonode  (cost=0.00..3.01 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $1)
                       ->  Index Scan using runway_geonode_id on runway
geonode  (cost=0.00..3.01 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $1)
                       ->  Index Scan using vhf_navaid_geonode_id on
vhf_navaid geonode  (cost=0.00..3.01 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $1)
           ->  Result  (cost=0.00..29.58 rows=9 width=16) (never executed)
                 ->  Append  (cost=0.00..29.58 rows=9 width=16) (never
executed)
                       ->  Index Scan using geonode_pkey on geonode
(cost=0.00..4.82 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $0)
                       ->  Index Scan using airport_geonode_id on airport
geonode  (cost=0.00..3.48 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $0)
                       ->  Index Scan using
airport_communications_geonode_id on airport_communications geonode
(cost=0.00..3.01 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $0)
                       ->  Index Scan using airport_waypoint_geonode_id on
airport_waypoint geonode  (cost=0.00..3.20 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $0)
                       ->  Index Scan using enroute_waypoint_geonode_id on
enroute_waypoint geonode  (cost=0.00..3.01 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $0)
                       ->  Index Scan using ils_navaid_geonode_id on
ils_navaid geonode  (cost=0.00..3.01 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $0)
                       ->  Index Scan using ndb_navaid_geonode_id on
ndb_navaid geonode  (cost=0.00..3.01 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $0)
                       ->  Index Scan using runway_geonode_id on runway
geonode  (cost=0.00..3.01 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $0)
                       ->  Index Scan using vhf_navaid_geonode_id on
vhf_navaid geonode  (cost=0.00..3.01 rows=1 width=16) (never executed)
                             Index Cond: (geonode_id = $0)
   SubPlan
     ->  Result  (cost=0.00..29.58 rows=9 width=16) (actual
time=0.014..0.056 rows=1 loops=412)
           ->  Append  (cost=0.00..29.58 rows=9 width=16) (actual
time=0.011..0.052 rows=1 loops=412)
                 ->  Index Scan using geonode_pkey on geonode
(cost=0.00..4.82 rows=1 width=16) (actual time=0.002..0.002 rows=0
loops=412)
                       Index Cond: (geonode_id = $1)
                 ->  Index Scan using airport_geonode_id on airport geonode
(cost=0.00..3.48 rows=1 width=16) (actual time=0.006..0.007 rows=1
loops=412)
                       Index Cond: (geonode_id = $1)
                 ->  Index Scan using airport_communications_geonode_id on
airport_communications geonode  (cost=0.00..3.01 rows=1 width=16) (actual
time=0.005..0.005 rows=0 loops=412)
                       Index Cond: (geonode_id = $1)
                 ->  Index Scan using airport_waypoint_geonode_id on
airport_waypoint geonode  (cost=0.00..3.20 rows=1 width=16) (actual
time=0.004..0.004 rows=0 loops=412)
                       Index Cond: (geonode_id = $1)
                 ->  Index Scan using enroute_waypoint_geonode_id on
enroute_waypoint geonode  (cost=0.00..3.01 rows=1 width=16) (actual
time=0.004..0.004 rows=0 loops=412)
                       Index Cond: (geonode_id = $1)
                 ->  Index Scan using ils_navaid_geonode_id on ils_navaid
geonode  (cost=0.00..3.01 rows=1 width=16) (actual time=0.004..0.004 rows=0
loops=412)
                       Index Cond: (geonode_id = $1)
                 ->  Index Scan using ndb_navaid_geonode_id on ndb_navaid
geonode  (cost=0.00..3.01 rows=1 width=16) (actual time=0.004..0.004 rows=0
loops=412)
                       Index Cond: (geonode_id = $1)
                 ->  Index Scan using runway_geonode_id on runway geonode
(cost=0.00..3.01 rows=1 width=16) (actual time=0.004..0.004 rows=0
loops=412)
                       Index Cond: (geonode_id = $1)
                 ->  Index Scan using vhf_navaid_geonode_id on vhf_navaid
geonode  (cost=0.00..3.01 rows=1 width=16) (actual time=0.004..0.004 rows=0
loops=412)
                       Index Cond: (geonode_id = $1)
     ->  Result  (cost=0.00..29.58 rows=9 width=16) (actual
time=0.015..0.058 rows=1 loops=412)
           ->  Append  (cost=0.00..29.58 rows=9 width=16) (actual
time=0.012..0.053 rows=1 loops=412)
                 ->  Index Scan using geonode_pkey on geonode
(cost=0.00..4.82 rows=1 width=16) (actual time=0.003..0.003 rows=0
loops=412)
                       Index Cond: (geonode_id = $0)
                 ->  Index Scan using airport_geonode_id on airport geonode
(cost=0.00..3.48 rows=1 width=16) (actual time=0.006..0.007 rows=1
loops=412)
                       Index Cond: (geonode_id = $0)
                 ->  Index Scan using airport_communications_geonode_id on
airport_communications geonode  (cost=0.00..3.01 rows=1 width=16) (actual
time=0.005..0.005 rows=0 loops=412)
                       Index Cond: (geonode_id = $0)
                 ->  Index Scan using airport_waypoint_geonode_id on
airport_waypoint geonode  (cost=0.00..3.20 rows=1 width=16) (actual
time=0.004..0.004 rows=0 loops=412)
                       Index Cond: (geonode_id = $0)
                 ->  Index Scan using enroute_waypoint_geonode_id on
enroute_waypoint geonode  (cost=0.00..3.01 rows=1 width=16) (actual
time=0.004..0.004 rows=0 loops=412)
                       Index Cond: (geonode_id = $0)
                 ->  Index Scan using ils_navaid_geonode_id on ils_navaid
geonode  (cost=0.00..3.01 rows=1 width=16) (actual time=0.004..0.004 rows=0
loops=412)
                       Index Cond: (geonode_id = $0)
                 ->  Index Scan using ndb_navaid_geonode_id on ndb_navaid
geonode  (cost=0.00..3.01 rows=1 width=16) (actual time=0.004..0.004 rows=0
loops=412)
                       Index Cond: (geonode_id = $0)
                 ->  Index Scan using runway_geonode_id on runway geonode
(cost=0.00..3.01 rows=1 width=16) (actual time=0.004..0.004 rows=0
loops=412)
                       Index Cond: (geonode_id = $0)
                 ->  Index Scan using vhf_navaid_geonode_id on vhf_navaid
geonode  (cost=0.00..3.01 rows=1 width=16) (actual time=0.004..0.004 rows=0
loops=412)
                       Index Cond: (geonode_id = $0)
 Total runtime: 60.119 ms
(132 rows)

test 4 superclass, join
explain analyze
select airport.airport_id, airport.name, seq_no
  from  airport, route_leg_tmp, geonode g1, geonode g2
  where box(airport.node,airport.node) && bounding_box(g1.node, g2.node,
30.0)
  and gc_offroute(g1.node, g2.node, airport.node) < 30.0
and route = 2
and start_id = g1.geonode_id
and end_id   = g2.geonode_id;
                                                                         QUERY
PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=16218.72..1319268499.15 rows=126889605 width=24) (actual
time=699.803..1367.254 rows=126 loops=1)
   Join Filter: (gc_offroute("outer".node, "outer".node, "inner".node) <
30::double precision)
   ->  Hash Join  (cost=16216.43..846068.41 rows=4611652 width=36) (actual
time=699.023..1359.460 rows=6 loops=1)
         Hash Cond: ("outer".geonode_id = "inner".start_id)
         ->  Append  (cost=0.00..14834.48 rows=175348 width=20) (actual
time=1.262..546.943 rows=174503 loops=1)
               ->  Seq Scan on geonode g1  (cost=0.00..16.50 rows=650
width=20) (actual time=0.002..0.002 rows=0 loops=1)
               ->  Seq Scan on airport g1  (cost=0.00..2536.09 rows=16509
width=20) (actual time=1.257..42.097 rows=16509 loops=1)
               ->  Seq Scan on airport_communications g1  (cost=0.00..742.55
rows=11855 width=20) (actual time=0.025..19.324 rows=11855 loops=1)
               ->  Seq Scan on airport_waypoint g1  (cost=0.00..2048.75
rows=28975 width=20) (actual time=0.016..48.997 rows=28975 loops=1)
               ->  Seq Scan on enroute_waypoint g1  (cost=0.00..6162.84
rows=73384 width=20) (actual time=26.347..137.920 rows=73189 loops=1)
               ->  Seq Scan on ils_navaid g1  (cost=0.00..421.72 rows=3472
width=20) (actual time=0.023..7.718 rows=3472 loops=1)
               ->  Seq Scan on ndb_navaid g1  (cost=0.00..857.86 rows=8086
width=20) (actual time=0.025..16.332 rows=8086 loops=1)
               ->  Seq Scan on runway g1  (cost=0.00..1241.88 rows=26388
width=20) (actual time=0.024..38.679 rows=26388 loops=1)
               ->  Seq Scan on vhf_navaid g1  (cost=0.00..806.29 rows=6029
width=20) (actual time=0.026..14.019 rows=6029 loops=1)
         ->  Hash  (cost=16203.28..16203.28 rows=5260 width=24) (actual
time=683.843..683.843 rows=6 loops=1)
               ->  Hash Join  (cost=1.09..16203.28 rows=5260 width=24)
(actual time=15.878..683.828 rows=6 loops=1)
                     Hash Cond: ("outer".geonode_id = "inner".end_id)
                     ->  Append  (cost=0.00..14834.48 rows=175348 width=20)
(actual time=0.087..553.947 rows=174503 loops=1)
                           ->  Seq Scan on geonode g2  (cost=0.00..16.50
rows=650 width=20) (actual time=0.002..0.002 rows=0 loops=1)
                           ->  Seq Scan on airport g2  (cost=0.00..2536.09
rows=16509 width=20) (actual time=0.083..45.540 rows=16509 loops=1)
                           ->  Seq Scan on airport_communications g2
(cost=0.00..742.55 rows=11855 width=20) (actual time=0.021..19.947
rows=11855 loops=1)
                           ->  Seq Scan on airport_waypoint g2
(cost=0.00..2048.75 rows=28975 width=20) (actual time=0.025..49.609
rows=28975 loops=1)
                           ->  Seq Scan on enroute_waypoint g2
(cost=0.00..6162.84 rows=73384 width=20) (actual time=26.250..134.931
rows=73189 loops=1)
                           ->  Seq Scan on ils_navaid g2  (cost=0.00..421.72
rows=3472 width=20) (actual time=0.028..7.621 rows=3472 loops=1)
                           ->  Seq Scan on ndb_navaid g2  (cost=0.00..857.86
rows=8086 width=20) (actual time=0.040..16.490 rows=8086 loops=1)
                           ->  Seq Scan on runway g2  (cost=0.00..1241.88
rows=26388 width=20) (actual time=0.027..38.942 rows=26388 loops=1)
                           ->  Seq Scan on vhf_navaid g2  (cost=0.00..806.29
rows=6029 width=20) (actual time=0.029..13.836 rows=6029 loops=1)
                     ->  Hash  (cost=1.07..1.07 rows=6 width=12) (actual
time=0.035..0.035 rows=6 loops=1)
                           ->  Seq Scan on route_leg_tmp  (cost=0.00..1.07
rows=6 width=12) (actual time=0.014..0.023 rows=6 loops=1)
                                 Filter: (route = 2)
   ->  Bitmap Heap Scan on airport  (cost=2.29..284.02 rows=83 width=36)
(actual time=0.107..0.226 rows=69 loops=6)
         Recheck Cond: (box(airport.node, airport.node) &&
bounding_box("outer".node, "outer".node, 30::double precision))
         ->  Bitmap Index Scan on geonode_node  (cost=0.00..2.29 rows=83
width=0) (actual time=0.097..0.097 rows=69 loops=6)
               Index Cond: (box(airport.node, airport.node) &&
bounding_box("outer".node, "outer".node, 30::double precision))
 Total runtime: 1367.578 ms
(35 rows)


pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Inheritance, unique keys and performance
Next
From: Tom Lane
Date:
Subject: Re: Inheritance, unique keys and performance