UNION ALL vs INHERITANCE - Mailing list pgsql-hackers

Greetings,

Why does the append resulting from a inheritance take longer than one
resulting from  UNION ALL?

summary:
Append resulting from inheritance:
->  Append  (cost=0.00..17.43 rows=2 width=72) (actual
time=3.876..245.320 rows=28 loops=1)
Append resulting from UNION ALL:
->  Append  (cost=0.00..17.45 rows=2 width=72) (actual
time=3.730..81.465 rows=28 loops=1)

in the case below both f_f_all_base and for_f_all_new are  clustered on
the index based (group_id, group_forum_id) they were vacuum analyzed
before the test below.

perftestdb=# \d f_f_all_base
               Table "public.f_f_all_base"
      Column     |   Type   |         Modifiers
----------------+----------+---------------------------
  msg_id         | integer  | not null
  group_id       | integer  | default 0
  group_forum_id | integer  | not null default 0
  subject        | text     | not null default ''::text
  date           | integer  | not null default 0
  user_name      | text     | not null default ''::text
  all_tidx       | tsvector | not null
Indexes:
     "forftiallb_pk_1102715767" primary key, btree (msg_id)
     "fftiallbgfid_1102715649" btree (group_forum_id)
     "fftiallbgrgfid_1102715649" btree (group_id, group_forum_id)

perftestdb=# \d for_f_all_new
              Table "public.for_f_all_new"
      Column     |   Type   |         Modifiers
----------------+----------+---------------------------
  msg_id         | integer  | not null
  group_id       | integer  | default 0
  group_forum_id | integer  | not null default 0
  subject        | text     | not null default ''::text
  date           | integer  | not null default 0
  user_name      | text     | not null default ''::text
  all_tidx       | tsvector | not null
Indexes:
     "forfallnew_pk_ts" primary key, btree (msg_id)
     "forfallnewgrgfid" btree (group_id, group_forum_id)
     "forfallnewgrid" btree (group_forum_id)
Inherits: f_f_all_base

perftestdb=# explain analyze (SELECT f_f_all_base.msg_id,
f_f_all_base.subject, f_f_all_base.date, f_f_all_base.user_name, '' as
fromemail FROM f_f_all_base WHERE (all_tidx @@ to_tsquery('MMcache') )
AND f_f_all_base.group_id = 78745) ORDER BY msg_id DESC LIMIT 26 OFFSET
0;

       QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
-----------------------
  Limit  (cost=17.44..17.44 rows=2 width=72) (actual
time=245.726..245.827 rows=26 loops=1)
    ->  Sort  (cost=17.44..17.44 rows=2 width=72) (actual
time=245.719..245.755 rows=26 loops=1)
          Sort Key: public.f_f_all_base.msg_id
          ->  Result  (cost=0.00..17.43 rows=2 width=72) (actual
time=3.885..245.564 rows=28 loops=1)
                ->  Append  (cost=0.00..17.43 rows=2 width=72) (actual
time=3.876..245.320 rows=28 loops=1)
                      ->  Index Scan using fftiallbgrgfid_1102715649 on
f_f_all_base  (cost=0.00..3.52 rows=1 width=51) (actual
time=3.871..244.356 rows=28 loops=1)
                            Index Cond: (group_id = 78745)
                            Filter: (all_tidx @@ '\'mmcach\''::tsquery)
                      ->  Index Scan using forfallnewgrgfid on
for_f_all_new f_f_all_base  (cost=0.00..13.91 rows=1 width=72) (actual
time=0.816..0.816 rows=0 loops=1)
                            Index Cond: (group_id = 78745)
                            Filter: (all_tidx @@ '\'mmcach\''::tsquery)
  Total runtime: 246.022 ms
(12 rows)

perftestdb=# explain analyze (SELECT f_f_all_base.msg_id,
f_f_all_base.subject, f_f_all_base.date, f_f_all_base.user_name, '' as
fromemail FROM ONLY f_f_all_base WHERE (all_tidx @@
to_tsquery('MMcache') ) AND f_f_all_base.group_id = 78745) UNION ALL
(SELECT f_f_all_new.msg_id, f_f_all_new.subject, f_f_all_new.date,
f_f_all_new.user_name, '' as fromemail FROM for_f_all_new f_f_all_new
WHERE (all_tidx @@ to_tsquery('MMcache') ) AND f_f_all_new.group_id  =
78745) ORDER BY msg_id DESC LIMIT 26 OFFSET 0;

       QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
----------------------
  Limit  (cost=17.46..17.46 rows=2 width=72) (actual time=81.703..81.833
rows=26 loops=1)
    ->  Sort  (cost=17.46..17.46 rows=2 width=72) (actual
time=81.695..81.737 rows=26 loops=1)
          Sort Key: msg_id
          ->  Append  (cost=0.00..17.45 rows=2 width=72) (actual
time=3.730..81.465 rows=28 loops=1)
                ->  Subquery Scan "*SELECT* 1"  (cost=0.00..3.53 rows=1
width=51) (actual time=3.726..80.213 rows=28 loops=1)
                      ->  Index Scan using fftiallbgrgfid_1102715649 on
f_f_all_base  (cost=0.00..3.52 rows=1 width=51) (actual
time=3.714..79.996 rows=28 loops=1)
                            Index Cond: (group_id = 78745)
                            Filter: (all_tidx @@ '\'mmcach\''::tsquery)
                ->  Subquery Scan "*SELECT* 2"  (cost=0.00..13.92 rows=1
width=72) (actual time=1.146..1.146 rows=0 loops=1)
                      ->  Index Scan using forfallnewgrgfid on
for_f_all_new f_f_all_new  (cost=0.00..13.91 rows=1 width=72) (actual
time=1.135..1.135 rows=0 loops=1)
                            Index Cond: (group_id = 78745)
                            Filter: (all_tidx @@ '\'mmcach\''::tsquery)
  Total runtime: 82.108 ms
(13 rows)
--
Adi Alurkar (DBA sf.NET) <adi@vasoftware.com>
1024D/79730470 A491 5724 74DE 956D 06CB  D844 6DF1 B972 7973 0470