Re: UNION ALL has higher cost than inheritance

On Thu, Oct 21, 2010 at 2:18 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> The plan for UNION initially involves a couple of SubqueryScan nodes,
> which impose an extra cost of cpu_tuple_cost per tuple.  Those later
> get optimized away, but we don't try to readjust the cost estimates
> for that.

Thanks. It also explains my another question why Merge Append cannot
be used for UNION ALL plans. Inheritance is better than UNION ALL
in much more cases thanks to Merge Append.

=# EXPLAIN SELECT * FROM parent ORDER BY i LIMIT 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Limit (cost=1.02..1.58 rows=10 width=4)
-> Result (cost=1.02..56.79 rows=1001 width=4)
-> Merge Append (cost=1.02..56.79 rows=1001 width=4)
Sort Key: public.parent.i
-> Sort (cost=1.01..1.01 rows=1 width=4)
Sort Key: public.parent.i
-> Seq Scan on parent (cost=0.00..1.00 rows=1 width=4)
-> Index Scan using child_i_idx on child parent
(cost=0.00..43.25 rows=1000 width=4)
(8 rows)

=# EXPLAIN (SELECT * FROM ONLY parent ORDER BY i) UNION ALL (SELECT *
FROM child ORDER BY i) ORDER BY i LIMIT 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Limit (cost=75.91..75.93 rows=10 width=4)
-> Sort (cost=75.91..78.41 rows=1001 width=4)
Sort Key: parent.i
-> Append (cost=1.01..54.28 rows=1001 width=4)
-> Sort (cost=1.01..1.01 rows=1 width=4)
Sort Key: parent.i
-> Seq Scan on parent (cost=0.00..1.00 rows=1 width=4)
-> Index Scan using child_i_idx on child
(cost=0.00..43.25 rows=1000 width=4)
(8 rows)

--
Itagaki Takahiro