Re: Invalid query generated by postgres_fdw with UNION ALL and ORDER BY

On Fri, Mar 8, 2024 at 7:43 AM David Rowley wrote: > On Fri, 8 Mar 2024 at 00:54, Ashutosh Bapat > wrote: > > > > On Thu, Mar 7, 2024 at 4:39 PM David Rowley > wrote: > >> I think the fix should go in appendOrderByClause(). It's at that > >> point we look for the EquivalenceMember for the

Re: Invalid query generated by postgres_fdw with UNION ALL and ORDER BY

d as constant even if the child expr is a Const, as explained in > add_child_rel_equivalences(). This situation where the child member is a Const but the parent isn't is unique to UNION ALL queries. The only other cases where we have child members are with partitioned and inheritance tables. In th

Re: Invalid query generated by postgres_fdw with UNION ALL and ORDER BY

On Fri, Mar 8, 2024 at 10:13 AM David Rowley wrote: > The fix could also be to use deparseConst() in appendOrderByClause() > and have that handle Const EquivalenceMember instead. I'd rather just > skip them. To me, that seems less risky than ensuring deparseConst() > handles all Const types

Re: Invalid query generated by postgres_fdw with UNION ALL and ORDER BY

em_expr = em->em_expr; + /* +* If the member is a Const expression then we needn't add it to the +* ORDER BY clause. This can happen in UNION ALL queries where the +* union child targetlist has a Const. Adding these would

Re: Invalid query generated by postgres_fdw with UNION ALL and ORDER BY

On Thu, Mar 7, 2024 at 4:39 PM David Rowley wrote: > On Thu, 7 Mar 2024 at 19:09, Michał Kłeczek wrote: > > > > The following query: > > > > SELECT * FROM ( > > SELECT 2023 AS year, * FROM remote_table_1 > > UNION ALL > > SELECT 2022 AS year,

Re: Invalid query generated by postgres_fdw with UNION ALL and ORDER BY

On Thu, 7 Mar 2024 at 19:09, Michał Kłeczek wrote: > > The following query: > > SELECT * FROM ( > SELECT 2023 AS year, * FROM remote_table_1 > UNION ALL > SELECT 2022 AS year, * FROM remote_table_2 > ) > ORDER BY year DESC; > > yields the following remote

Invalid query generated by postgres_fdw with UNION ALL and ORDER BY

The following query: SELECT * FROM ( SELECT 2023 AS year, * FROM remote_table_1 UNION ALL SELECT 2022 AS year, * FROM remote_table_2 ) ORDER BY year DESC; yields the following remote query: SELECT [columns] FROM remote_table_1 ORDER BY 2023 DESC and subsequently fails remote execution

feature request: IN clause optimized through append nodes with UNION ALL

In the script below, the presence of an IN clause forces the internal components of the UNION ALL clause to fully compute even though they are fully optimizable. = ANY doesn't have this issue, so I wonder if there is any opportunity to convert the 'slow' variant (see below) to the 'fast' variant

Re: Optimization issue of branching UNION ALL

Andrey Lepikhov writes: > Thanks, I have written the letter because of some doubts too. But only > one weak point I could imagine - if someday sql standard will be changed. Yeah, if they ever decide that LATERAL should be allowed to reference a previous sub-query of UNION ALL, that'd pr

Re: Optimization issue of branching UNION ALL

On 22/12/2022 06:50, Tom Lane wrote: 2. Iterative passes along the append_rel_list for replacing vars in the translated_vars field. I can't grasp real necessity of passing all the append_rel_list during flattening of an union all leaf subquery. No one can reference this leaf, isn't it? After

Re: Optimization issue of branching UNION ALL

I wrote: > Richard Guo writes: >> I noticed we also check 'parse->hasSubLinks' when we fix PHVs and >> AppendRelInfos in pull_up_simple_subquery. I'm not sure why we have >> this check. It seems not necessary. > Yeah, I was wondering about that too ... maybe it was important > in some previous

Re: Optimization issue of branching UNION ALL

Richard Guo writes: > I noticed we also check 'parse->hasSubLinks' when we fix PHVs and > AppendRelInfos in pull_up_simple_subquery. I'm not sure why we have > this check. It seems not necessary. Yeah, I was wondering about that too ... maybe it was important in some previous state of the

Re: Optimization issue of branching UNION ALL

On Thu, Dec 22, 2022 at 9:50 AM Tom Lane wrote: > Andrey Lepikhov writes: > > Superficial study revealed possibly unnecessary operations that could be > > avoided: > > 1. Walking across a query by calling substitute_phv_relids() even if > > lastPHId shows that no one phv is presented. > > Yeah,

Re: Optimization issue of branching UNION ALL

y needs tons of gigabytes of RAM. v11 (and prior versions) sucks just as badly. In this example it accidentally escapes trouble because it doesn't know how to pull up a subquery with empty FROM. But if you make the query look like SELECT 1,1 FROM dual UNION ALL SELECT 2,2 FROM dual UNION AL

Optimization issue of branching UNION ALL

Hi, Client report on a corner case have shown up possible minor non-optimality in procedure of transformation of simple UNION ALL statement tree. Complaint is about auto-generated query with 1E4 simple union all's (see t.sh to generate a demo script). The reason: in REL_11_STABLE

Re: allow partial union-all and improve parallel subquery costing

With the thread stalled and requests for a test (documentation really?) not responded to I'm marking this patch Returned with Feedback. -- Daniel Gustafsson https://vmware.com/

Re: allow partial union-all and improve parallel subquery costing

ions here are a couple remarks. I think you should add a test demonstrating the use of the new partial append path you add, for example using your base query: explain (costs off) select sum(two) from ( select *, 1::int from tenk1 a union all select *, 1::bigint from ten

Re: allow partial union-all and improve parallel subquery costing

and rebased patch. Hope the description helps some people. I will resubmit it to the next commitfest. Regards, Luc >From e918e7cf8c9fe628c7daba2ccf37ad767691e4c7 Mon Sep 17 00:00:00 2001 From: Luc Vlaming Date: Mon, 12 Apr 2021 09:55:30 +0200 Subject: [PATCH v4] Add explicit partial UNION ALL p

Re: allow partial union-all and improve parallel subquery costing

On 3/15/21 9:09 AM, David Steele wrote: On 12/30/20 8:54 AM, Luc Vlaming wrote: Created a commitfest entry assuming this is the right thing to do so that someone can potentially pick it up during the commitfest. Providing an updated patch based on latest master. Looks like you need

Re: allow partial union-all and improve parallel subquery costing

Hi Luc, On 12/30/20 8:54 AM, Luc Vlaming wrote: Created a commitfest entry assuming this is the right thing to do so that someone can potentially pick it up during the commitfest. Providing an updated patch based on latest master. Looks like you need another rebase:

Re: allow partial union-all and improve parallel subquery costing

: Tuesday, October 13, 2020 10:57 AM To: pgsql-hackers Subject: allow partial union-all and improve parallel subquery costing Hi, While developing some improvements for TPC-DS queries I found out that with UNION ALL partial paths are not emitted. Whilst fixing that I also came across the subquery

Re: Parallel plans and "union all" subquery

On 27-11-2020 04:14, Greg Nancarrow wrote: On Thu, Nov 26, 2020 at 6:11 PM Luc Vlaming wrote: If interesting I can make a draft of what this would look like if this makes it easier to discuss? Sure, that would help clarify it. Okay. I will try to build an example but this will take a few

Re: Parallel plans and "union all" subquery

On Thu, Nov 26, 2020 at 6:11 PM Luc Vlaming wrote: > > If interesting I can make a draft of what this would look like if this > makes it easier to discuss? > Sure, that would help clarify it. I did debug this a bit, but it seems my gut feeling was wrong, even though it knows a type coercion is

Re: Parallel plans and "union all" subquery

ct in his expressed approach on what would be required to fix it? Regards, Greg Nancarrow Fujitsu Australia So from what I recall from building the patch is that the difference is that when all types are identical, then flatten_simple_union_all simply flattens all union-all operations in

Re: Parallel plans and "union all" subquery

On Wed, Nov 25, 2020 at 6:43 PM Luc Vlaming wrote: > > > You're completely right, sorry for my error. I was too quick on assuming > my patch would work for this specific case too; I should have tested > that before replying. It looked very similar but turns out to not work > because of the upper

Re: Parallel plans and "union all" subquery

at it? I tried your patch with the latest PG source code (24/11), but unfortunately a non-parallel plan was still produced in this case. test=# explain select count(*) from (select n1 from drop_me union all values(1)) ua; QUERY PLAN

Re: Parallel plans and "union all" subquery

; I tried your patch with the latest PG source code (24/11), but unfortunately a non-parallel plan was still produced in this case. test=# explain select count(*) from (select n1 from drop_me union all values(1)) ua;

Re: Parallel plans and "union all" subquery

*De :* Greg Nancarrow *Envoyé :* lundi 23 novembre 2020 06:04 *À :* Phil Florent *Cc :* pgsql-hackers@lists.postgresql.org *Objet :* Re: Parallel plans and "union all" subquery On Sun, Nov 22, 2020 at 11:51 PM Phil Flore

RE: Parallel plans and "union all" subquery

Envoyé : lundi 23 novembre 2020 06:04 À : Phil Florent Cc : pgsql-hackers@lists.postgresql.org Objet : Re: Parallel plans and "union all" subquery On Sun, Nov 22, 2020 at 11:51 PM Phil Florent wrote: > > > Hi, > > > I have a question about parallel plans. I also p

Re: Parallel plans and "union all" subquery

On Sun, Nov 22, 2020 at 11:51 PM Phil Florent wrote: > > > Hi, > > > I have a question about parallel plans. I also posted it on the general list > but perhaps it's a question for hackers. Here is my test case : > > > explain > select count(*) > from (se

Parallel plans and "union all" subquery

idth=8) -> Parallel Seq Scan on drop_me (cost=0.00..601402.13 rows=29166713 width=0) JIT: Functions: 4 Options: Inlining true, Optimization true, Expressions true, Deforming true Parallel plan, 1s explain select count(*) from (select n1 from drop_me union all select n1 from drop_me) ua; Q

Re: allow partial union-all and improve parallel subquery costing

-hackers Subject: allow partial union-all and improve parallel subquery costing Hi, While developing some improvements for TPC-DS queries I found out that with UNION ALL partial paths are not emitted. Whilst fixing that I also came across the subquery costing which does not seem to consider

Re: allow partial union-all and improve parallel subquery costing

to make sure the regress output is stable. Updated patch attached which now successfully runs installcheck-world for v13 and master. Kind regards, Luc From: Luc Vlaming Sent: Tuesday, October 13, 2020 10:57 AM To: pgsql-hackers Subject: allow partial union-all

allow partial union-all and improve parallel subquery costing

Hi, While developing some improvements for TPC-DS queries I found out that with UNION ALL partial paths are not emitted. Whilst fixing that I also came across the subquery costing which does not seem to consider parallelism when doing the costing. I added a simplified testcase in pg-regress

Re: UNION ALL

Mark Pasterkamp writes: > I am comparing two queries, q1 and q2 respectively. > Query q1 is the original query and q2 is an attempt to reduce the cost of > execution via leveraging the materialized view ci_t_15. > ... > Running explain analyze on both queries I get the following execution plans.

Re: UNION ALL

On Fri, Aug 16, 2019 at 12:16 AM <066ce...@free.fr> wrote: > Generally speaking, when executing UNION ; a DISTINCT is run afterward on > the resultset. > > So, if you're sure that each part of UNION cannot return a line returned > by another one, you may use UNION ALL

Re: UNION ALL

Generally speaking, when executing UNION ; a DISTINCT is run afterward on the resultset. So, if you're sure that each part of UNION cannot return a line returned by another one, you may use UNION ALL, you'll cut the cost of the final implicit DISTINCT. - Mail original - De: "

Re: UNION ALL

Mark Pasterkamp writes: > I was wondering if someone could help me understands what a union all > actually does. Generally speaking, it runs the first query and then the second query. You'd really need to provide a lot more detail for anyone to say more than that. https://wiki.postgres

UNION ALL

Dear all, I was wondering if someone could help me understands what a union all actually does. For my thesis I am using Apache Calcite to rewrite queries into using materialized views which I then give to a Postgres database. For some queries, this means that they will be rewritten in a UNION

Re: Postgres 10 problem with UNION ALL of null value in "subselect"

2018-04-19 5:01 GMT+02:00 Kyotaro HORIGUCHI : > At Mon, 16 Apr 2018 18:39:24 +0530, Ashutosh Bapat < > ashutosh.ba...@enterprisedb.com> wrote in

Re: Postgres 10 problem with UNION ALL of null value in "subselect"

At Mon, 16 Apr 2018 18:39:24 +0530, Ashutosh Bapat wrote in

Re: Postgres 10 problem with UNION ALL of null value in "subselect"

Apple LLVM version > 7.0.0 (clang-700.1.76), 64-bit > > Simplified core of the problematic query looks like this: > ``` > select * from ( >select 1::integer as a > ) t1 > union all > select * from ( >select null as a > ) t2; > ``` > > It fails with th

Postgres 10 problem with UNION ALL of null value in "subselect"

* from ( select 1::integer as a ) t1 union all select * from ( select null as a ) t2; ``` It fails with this error message: ``` ERROR: UNION types integer and text cannot be matched LINE 5: select * from ( ^ SQL state: 42804 Character: 66 ``` It worked on PostgreSQL 9.6

Re: parallel append vs. simple UNION ALL

On Mon, Mar 19, 2018 at 11:57 AM, Robert Haas wrote: > On Fri, Mar 16, 2018 at 7:35 AM, Rajkumar Raghuwanshi > wrote: >> On Fri, Mar 9, 2018 at 1:04 AM, Robert Haas wrote: >>> Great. Committed 0001. Are you

Re: parallel append vs. simple UNION ALL

On Fri, Mar 16, 2018 at 7:35 AM, Rajkumar Raghuwanshi wrote: > On Fri, Mar 9, 2018 at 1:04 AM, Robert Haas wrote: >> Great. Committed 0001. Are you planning any further testing of this >> patch series? > > Sorry I missed the mail. >

Re: parallel append vs. simple UNION ALL

On Fri, Mar 9, 2018 at 1:04 AM, Robert Haas wrote: > Great. Committed 0001. Are you planning any further testing of this > patch series? Sorry I missed the mail. Yes, I have further tested patches and find no more issues. Thanks & Regards, Rajkumar Raghuwanshi QMG,

Re: parallel append vs. simple UNION ALL

On Wed, Mar 14, 2018 at 2:09 AM, Robert Haas wrote: > On Tue, Mar 13, 2018 at 12:35 AM, Ashutosh Bapat > wrote: >> It looks like it was not changed in all the places. make falied. I >> have fixed all the instances of these two functions in

Re: parallel append vs. simple UNION ALL

On Tue, Mar 13, 2018 at 12:35 AM, Ashutosh Bapat wrote: > It looks like it was not changed in all the places. make falied. I > have fixed all the instances of these two functions in the attached > patchset (only 0003 changes). Please check. Oops. Thanks. I'm

Re: parallel append vs. simple UNION ALL

tion The Postgres Database Company From 7e654c0ae30d867edea5a1a2ca8f7a17b05ed7c5 Mon Sep 17 00:00:00 2001 From: Robert Haas <rh...@postgresql.org> Date: Fri, 23 Feb 2018 11:53:07 -0500 Subject: [PATCH 1/4] Let Parallel Append over simple UNION ALL have partial subpaths. A simple UNION ALL gets flattened int

Re: parallel append vs. simple UNION ALL

8:30 AM, Ashutosh Bapat mailto:ashutosh.ba...@enterprisedb.com; target="_blank">ashutosh.ba...@enterprisedb.com wrote:On Sat, Feb 24, 2018 at 2:55 AM, Robert Haas mailto:robertmh...@gmail.com;>robertmh...@gmail.com wrote: Here's an extended series of patches that now handles both

Re: parallel append vs. simple UNION ALL

On Thu, Mar 8, 2018 at 2:46 AM, Rajkumar Raghuwanshi wrote: > On Thu, Mar 8, 2018 at 12:27 AM, Robert Haas wrote: >> >> New patches attached, fixing all 3 of the issues you reported: > > Thanks. new patches applied cleanly on head and

Re: parallel append vs. simple UNION ALL

e-rather-tha.patch Description: Binary data 0002-Let-Parallel-Append-over-simple-UNION-ALL-have-parti.patch Description: Binary data 0001-Correctly-assess-parallel-safety-of-tlists-when-SRFs.patch Description: Binary data

Re: parallel append vs. simple UNION ALL

On Tue, Feb 27, 2018 at 6:21 AM, Rajkumar Raghuwanshi wrote: > I have applied 0001 on pg-head, and while playing with regression tests, it > crashed with below test case. > > postgres=# SET min_parallel_table_scan_size=0; > SET > postgres=# SELECT * FROM

Re: parallel append vs. simple UNION ALL

On Sat, Feb 24, 2018 at 2:55 AM, Robert Haas <robertmh...@gmail.com> wrote: > > Here's an extended series of patches that now handles both the simple > UNION ALL case (where we flatten it) and the unflattened case: > The patches look clean. I particularly looked at

Re: parallel append vs. simple UNION ALL

there's a > lot more that could be done here, but I'm posting what I have for now > in the (perhaps vain) hope of getting some feedback. With this, you > can use Parallel Append for the UNION ALL step of a query like SELECT > .. UNION ALL .. SELECT ... EXCEPT SELECT ... > Hi, Wi

Re: parallel append vs. simple UNION ALL

> On Sat, Feb 24, 2018 at 2:55 AM, Robert Haas wrote: >> >> 0001 is pretty much the same as the subquery-smarts.patch file I >> attached to the previous email. I don't see much reason not to go >> ahead and commit this, although it could use a test case. It makes >> the

Re: parallel append vs. simple UNION ALL

On Sat, Feb 24, 2018 at 2:55 AM, Robert Haas wrote: > 0001 is pretty much the same as the subquery-smarts.patch file I > attached to the previous email. I don't see much reason not to go > ahead and commit this, although it could use a test case. It makes > the

Re: parallel append vs. simple UNION ALL

On Sat, Dec 23, 2017 at 4:53 PM, Robert Haas <robertmh...@gmail.com> wrote: > As I mentioned in the commit message for the Parallel Append commit > (ab72716778128fb63d54ac256adf7fe6820a1185), it's kind of sad that this > doesn't work with UNION ALL queries, which are an ob

parallel append vs. simple UNION ALL

As I mentioned in the commit message for the Parallel Append commit (ab72716778128fb63d54ac256adf7fe6820a1185), it's kind of sad that this doesn't work with UNION ALL queries, which are an obvious candidate for such parallelization. It turns out that it actually does work to a limited degree