Re: UNION ALL has higher cost than inheritance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: UNION ALL has higher cost than inheritance
Date: 2010-10-21 21:17:10
Message-ID: 6696.1287695830@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Greg Stark <gsstark(at)mit(dot)edu> writes:
> On Thu, Oct 21, 2010 at 6:57 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Thanks. It also explains my another question why Merge Append cannot
>>> be used for UNION ALL plans.

>> Hmm, seems like the example you show ought to work. I wonder if there
>> was an oversight in that patch...

> Huh, that definitely worked in the earlier versions of the patch (as
> much as it "worked" at all)

Actually, it works as long as the UNION is in a subquery:

regression=# EXPLAIN select * from (
(SELECT * FROM ONLY parent ORDER BY i) UNION ALL
(SELECT * FROM child ORDER BY i)) ss ORDER BY i LIMIT 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Limit (cost=168.76..169.13 rows=10 width=4)
-> Result (cost=168.76..294.51 rows=3400 width=4)
-> Merge Append (cost=168.76..294.51 rows=3400 width=4)
Sort Key: parent.i
-> Sort (cost=168.75..174.75 rows=2400 width=4)
Sort Key: parent.i
-> Seq Scan on parent (cost=0.00..34.00 rows=2400 width=4)
-> Index Scan using child_i_idx on child (cost=0.00..43.25 rows=1000 width=4)
(8 rows)

The oversight here is that we don't use appendrel planning for
a top-level UNION ALL construct. That didn't use to matter,
because you always got the same stupid Append plan either way.
Now it seems like we ought to have some more intelligence for the
top-level SetOp case. I smell some code refactoring coming up.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kevin Grittner 2010-10-21 21:21:30 Re: pg_rawdump
Previous Message Alvaro Herrera 2010-10-21 21:06:40 Re: find -path isn't portable