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 rela
; marked 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 inheritanc
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 corr
s, bool has_final_sort,
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 targe
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,
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
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
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
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'
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 i
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
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 code?
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,
query 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
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
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/
implications 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::b
d 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
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 anothe
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:
http://cfbot.cputube.o
: 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
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 we
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 r
d worth fixing, and whether Luc is correct 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 simp
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 r
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
d 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;
*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
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
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(*)
> fro
s=1 width=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
-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
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
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 to
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.
via leveraging the materialized view ci_t_15.
Query q1 is defined as:
select * from cast_info join title on cast_info.movie_id = title.id
Query q2 is defined as
select * from cast_info join title on cast_info.movie_id = title.id
where title.production_year >= 2015
UNION ALL
select * from ci_t_15
Both q
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,
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 -
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.pos
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
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 (
> > >sel
PostgreSQL:
> > PostgreSQL 10.3 on x86_64-apple-darwin14.5.0, compiled by 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::inte
(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 this error message:
> ```
* 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
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 planning any further testing of this
>>> patch series?
>>
>> Sorry I missed the mail.
>
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.
> Yes, I have further tested patches and find no more issues.
O
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, EnterpriseDB Corporation
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 the attached
>> patchset (only 0003 changes). Please chec
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 going to go ahead and commit 0001 he
ion
The Postgres Database Company
From 7e654c0ae30d867edea5a1a2ca8f7a17b05ed7c5 Mon Sep 17 00:00:00 2001
From: Robert Haas
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 into an appendrel of subquery
RTEs, but up until now it&
PostgreSQL CompanyOn Thu, Mar 1, 2018 at 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
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 fixing all reported issue.
Great. Committed 0001. Are you p
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 fixing all reported issue.
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation
y 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
=1)
UNION All
SELECT a FROM bar where a=(select * from bar where a=1);
/*
postgres=# Select a from foo where a=(select * from foo where a=1)
UNION All
SELECT a FROM bar where a=(select * from bar where a=1);
WARNING: relcache reference leak: relation "foo" not closed
WARNING: relcache
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 information_schema.foreign_data_wrapper_opt
On Sat, Feb 24, 2018 at 2:55 AM, Robert Haas 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 0003.
patch 0001
+/*
+ *
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,
With all 0001,0002,0
> 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 simple/flattened case wor
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 simple/flattened case work. Afte
On Sat, Dec 23, 2017 at 4:53 PM, Robert Haas 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 obvious candidate
> for suc
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 limi
62 matches
Mail list logo