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) <[EMAIL PROTECTED]>
1024D/79730470 A491 5724 74DE 956D 06CB D844 6DF1 B972 7973 0470



---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Reply via email to