Re: UNION ALL

2019-08-16 Thread Tom Lane
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

2019-08-16 Thread Mark Pasterkamp
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

Re: UNION ALL

2019-08-15 Thread Ibrar Ahmed
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

2019-08-15 Thread 066ce286
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

2019-08-15 Thread Tom Lane
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

2019-08-15 Thread Mark Pasterkamp
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-18 Thread Pavel Stehule
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"

2018-04-18 Thread Kyotaro HORIGUCHI
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"

2018-04-16 Thread Ashutosh Bapat
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"

2018-04-16 Thread Martin Swiech
* 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

2018-03-22 Thread Robert Haas
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

2018-03-19 Thread Robert Haas
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

2018-03-16 Thread Rajkumar Raghuwanshi
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

2018-03-16 Thread Ashutosh Bapat
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

2018-03-13 Thread Robert Haas
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

2018-03-12 Thread Ashutosh Bapat
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

2018-03-08 Thread Robert Haas
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

2018-03-08 Thread Robert Haas
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

2018-03-07 Thread Rajkumar Raghuwanshi
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

Re: parallel append vs. simple UNION ALL

2018-03-07 Thread Robert Haas
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

2018-03-05 Thread Robert Haas
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

2018-03-01 Thread Ashutosh Bapat
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

2018-03-01 Thread Rajkumar Raghuwanshi
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

2018-02-28 Thread Amit Khandekar
> 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

2018-02-27 Thread Rajkumar Raghuwanshi
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

2018-02-23 Thread Robert Haas
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

2017-12-23 Thread Robert Haas
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