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
