pushing order by + limit to union subqueries

From: Paolo Losi <paolo(dot)losi(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: pushing order by + limit to union subqueries
Date: 2015-02-28 09:08:30
Message-ID: CAP=2L=FRza_catqP9LRfJOMybzoorpxSBxSP=J1o9WZnFp1USg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

Hi all,
I've noticed that order by / limit are not distributed to union subqueries
by the planner:

Example:

q1: (select * from t1) union all (select * from t2) order by x limit 10;
q2: (select * from t1 order by x limit 10) union all (select * from t2
order by x limit 10)
order by x limit 10;

both queries should be equivalent, but the planner provides hugely different
plans. I was expecting that the planner could rewrite the first to the
second.
Am I overlooking something? If this is the case, can anyone explain why this
optimization is not performed?

Thanks!
Paolo

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2015-02-28 11:08:45 Re: Strange assertion using VACOPT_FREEZE in vacuum.c
Previous Message Marc Cousin 2015-02-28 08:03:52 Re: star schema and the optimizer

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2015-02-28 16:24:01 Re: pushing order by + limit to union subqueries
Previous Message Josh Berkus 2015-02-28 01:28:06 Bad cost estimate with FALSE filter condition