Thread: Inheritance, unique keys and performance

Useing 8.1.9 I'm running into some performance issues with inheritance.
I've abstracted the situation below because otherwise there's lots of
spurious stuff, but I'll gladly provide the real EXPLAIN ANALYZE output if
necessary.

I have a superclass and a dozen subclasses, of which I've picked one as an
example here.  The 'point' type in the class is not, as far as I can see,
relevant to the issue -- it could be any type.

create table superclass (
  id   integer PRIMARY KEY,
  node point,
  ...
)

create table subclass (
...
) INHERITS (superclass);
CREATE UNIQUE INDEX subclass_id ON subclass USING btree (id);

create table some_table (
  node point,
  ...
)

I perform a query on some_table using an expensive function with two of its
three point parameters looked up in the subclass (as an id -> node lookup
table). The first two point parameters to expensive_function are effectively
constants looked up once. I can structure it using scalar subqueries q1, or
as a join q2. Both are quick, the join is a little quicker.

-- q1 scalar subqueries
select *
  from some_table
  where
  and expensive_function(
    (select node from subclass where id = 101),
    (select node from subclass where id = 102),
    some_table.node);

-- q2 join
select *
  from some_table, subclass g1, subclass g2
  where expensive_function(g1.node, g2.node, some_table.node)
and  g1.id = 101
and  g2.id = 102;

Now what if I use the superclass?  The scalar subquery strategy q3 is fine.
The result of the subquery is unique because it's a scalar subquery, and the
planner knows that:

-- q3 scalar subqueries using superclass
select *
  from some_table
  where
  and expensive_function(
    (select node from superclass where id = 101),
    (select node from superclass where id = 102),
    some_table.node);

But the join q4 is a disaster.

-- q4 join join using superclass
select *
  from some_table, superclass g1, superclass g2
  where expensive_function(g1.node, g2.node, some_table.node)
and  g1.id = 101
and  g2.id = 102;

And I *think* I can see why -- I hope I'm not trying to be too smart here ;)
: superclass.id is not guaranteed to be unique, and the planner must cater
for multiple rows where g1.id = 101, and multiple rows where g2.id = 102
across the dozen tables comprising superclass.  So it picks a different
strategy involving sequential scans of all the superclass tables (even
though they have been ANALYZED) which is 100 times slower.

So the scalar-subqueries method is the only one I can use for the
superclass.  That's all very well as a workaround, but what I really want to
do is a further join.  Here are the queries using the subclass.

create table other_table (
  route       integer,
  leg_no     integer,
  start_id    integer,
  end_id     integer
)


-- q5 scalar subqueries
select some_table.*
  from some_table, other_table
  where
  and expensive_function(
    (select node from subclass where id = start_id),
    (select node from subclass where id = end_id),
    some_table.node)
  and other_table.route = 1;

-- q6 join
select some_table.*
  from some_table, other_table, subclass g1, subclass g2
  where expensive_function(g1.node, g2.node, some_table.node)
  and other_table.route = 1
  and other_table.start_id = g1.id
  and other_table.end_id   = g2.id;

When I test this on the subclass, as the "route" acquires more and more
"legs", the join q6 outperforms q5 by more and more.

-- q7 join
select some_table.*
  from some_table, other_table, superclass g1, superclass g2
  where expensive_function(g1.node, g2.node, some_table.node)
  and other_table.route = 1
  and other_table.start_id = g1.id
  and other_table.end_id   = g2.id;

So is there some way I can hint to the planner in q7 that superclass.id is
unique and that all it has to do is use superclass as an id -> node lookup
table?

Thanks

Julian

"Julian Scarfe" <julian@avbrief.com> writes:
> Useing 8.1.9 I'm running into some performance issues with inheritance.
> I've abstracted the situation below because otherwise there's lots of
> spurious stuff, but I'll gladly provide the real EXPLAIN ANALYZE output if
> necessary.

Without the EXPLAIN ANALYZE output, nobody can say whether you have
interpreted your performance problem correctly or not.

            regards, tom lane

> 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)

"Julian Scarfe" <julian@avbrief.com> writes:
>> Without the EXPLAIN ANALYZE output, nobody can say whether you have
>> interpreted your performance problem correctly or not.

> Fair enough, Tom.

Okay, so the "expensive function" isn't as expensive as all that ---
it seems to be adding only a few msec to the total runtime.  The
problem you've got is that you need a nestloop-with-inner-indexscan
plan type, where the inner side is an Append group (that is, an
inheritance tree) --- and 8.1 doesn't know how to create such a plan.
If you can update to 8.2 or later it should get better.

            regards, tom lane

From: "Tom Lane" <tgl@sss.pgh.pa.us>
>  The
> problem you've got is that you need a nestloop-with-inner-indexscan
> plan type, where the inner side is an Append group (that is, an
> inheritance tree) --- and 8.1 doesn't know how to create such a plan.
> If you can update to 8.2 or later it should get better.

Isn't test 3 (scalar subqueries) also a nestloop-with-inner-indexscan with
an inner Append?

Julian

"Julian Scarfe" <julian@avbrief.com> writes:
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
>> The
>> problem you've got is that you need a nestloop-with-inner-indexscan
>> plan type, where the inner side is an Append group (that is, an
>> inheritance tree) --- and 8.1 doesn't know how to create such a plan.
>> If you can update to 8.2 or later it should get better.

> Isn't test 3 (scalar subqueries) also a nestloop-with-inner-indexscan with
> an inner Append?

No.  The subquery is planned separately and sees the upper query's
variable as a pseudo-constant Param.  In neither query does the
planner think that a join is happening.

            regards, tom lane