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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Adi Alurkar <adi@sf.net> writes:
> Why does the append resulting from a inheritance take longer than one
> resulting from UNION ALL?

The index scan is where the time difference is:

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

One would have to suppose this is a caching effect, ie, the data is
already in RAM on the second try and doesn't have to be read from disk
again.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend