Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-04-15 Thread Kyotaro HORIGUCHI
Thank you for committing. At Fri, 28 Mar 2014 11:50:56 -0400, Tom Lane t...@sss.pgh.pa.us wrote in 21426.1396021...@sss.pgh.pa.us tgl Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp writes: tgl Hello. Attached is the 2nd version of 'pushdown in UNION ALL on tgl partitioned tables' patch type

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-03-28 Thread Tom Lane
Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp writes: Hello. Attached is the 2nd version of 'pushdown in UNION ALL on partitioned tables' patch type 1 - fix in equiv-member version. Committed, thanks. regards, tom lane -- Sent via pgsql-hackers mailing list

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-03-10 Thread Kyotaro HORIGUCHI
Hello. Attached is the 2nd version of 'pushdown in UNION ALL on partitioned tables' patch type 1 - fix in equiv-member version. As far as I can see, I have found no problem on the original Tom's patch. I have no annoyance of modifying inh flag and so with this. At Tue, 04 Mar 2014 18:57:56

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-03-05 Thread Tom Lane
Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp writes: ec_relids has never included child relids. relation.h says that, |Relids ec_relids; /* all relids appearing in ec_members */ ... |Relids em_relids; /* all relids appearing in em_expr */ Ah. Those comments could

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-03-04 Thread Kyotaro HORIGUCHI
Hello, I examined the your patch and it seemed reasonable, but I have one question about this patch. You made ec_relids differ to the union of all ec members' em_relids. Is it right? At Mon, 03 Mar 2014 14:05:10 -0500, Tom Lane wrote Noah Misch n...@leadboat.com writes: If you are

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-03-04 Thread Tom Lane
Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp writes: Hello, I examined the your patch and it seemed reasonable, but I have one question about this patch. You made ec_relids differ to the union of all ec members' em_relids. Is it right? ec_relids has never included child relids.

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-03-04 Thread Kyotaro HORIGUCHI
Hello, I haven't look closer on their relationship. Hello, I examined the your patch and it seemed reasonable, but I have one question about this patch. You made ec_relids differ to the union of all ec members' em_relids. Is it right? ec_relids has never included child relids.

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-03-03 Thread Kyotaro HORIGUCHI
Hello, Yes, the old dumped version of typ2 patch did so. It flattened appendrel tree for the query prpoerly. Let me hear the reson you prefer to do so. Having reviewed my upthread reasoning for preferring one of those two approaches over the other, it's a weak preference. They have

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-03-03 Thread Noah Misch
On Mon, Mar 03, 2014 at 07:01:09PM +0900, Kyotaro HORIGUCHI wrote: Yes, the old dumped version of typ2 patch did so. It flattened appendrel tree for the query prpoerly. Let me hear the reson you prefer to do so. Having reviewed my upthread reasoning for preferring one of those two

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-03-03 Thread Tom Lane
Noah Misch n...@leadboat.com writes: If you are convinced that a separate flattening pass is best, that suffices for me at this stage. Please submit the patch you have in mind, incorporating any improvements from the v7 patch that are relevant to both approaches. I went back and re-read the

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-03-03 Thread Kyotaro HORIGUCHI
Hello, Unfortunately, RTE_SUBQUERY-es with their inh flag cleard might cause something inconvenient in preprocess_minmax_aggregates() and/or add_rtes_to_flat_rtable().. preprocess_minmax_aggregates() only cares about RTEs reachable from the join tree, and the appendrel parents made

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-02-28 Thread Noah Misch
On Fri, Feb 28, 2014 at 02:35:50PM +0900, Kyotaro HORIGUCHI wrote: At Thu, 27 Feb 2014 21:53:52 -0500, Noah Misch wrote On Thu, Feb 27, 2014 at 05:33:47PM -0500, Tom Lane wrote: I wonder whether we should consider adding a pass to flatten any nested appendrels after we're done creating

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-02-27 Thread Kyotaro HORIGUCHI
Thank you for the labor for polishing this patch. I have no obvious objection at a glance on this new patch. I agree to commit this if you this is pertinent to commit except for the issue newly revealed by this patch. Though could you let me have some more time to examine this by myself and

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-02-27 Thread Noah Misch
On Thu, Feb 27, 2014 at 07:30:57PM +0900, Kyotaro HORIGUCHI wrote: Thank you for the labor for polishing this patch. I have no obvious objection at a glance on this new patch. I agree to commit this if you this is pertinent to commit except for the issue newly revealed by this patch.

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-02-27 Thread Tom Lane
Noah Misch n...@leadboat.com writes: If the attached patch version looks reasonable, I will commit it. The test case is completely bogus, as the query explained is significantly different from the query executed. I'm not sure whether you can just remove the extra ORDER BY column without getting

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-02-27 Thread Tom Lane
BTW, isn't the proposed change to the comments for adjust_appendrel_attrs just wrong? If it's correct, why doesn't the Assert(!IsA(node, SubLink)) therein fire? (AFAICS, the existing comment is correct: we don't use this function until after expression preprocessing is complete.)

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-02-27 Thread Noah Misch
On Thu, Feb 27, 2014 at 05:47:16PM -0500, Tom Lane wrote: BTW, isn't the proposed change to the comments for adjust_appendrel_attrs just wrong? If it's correct, why doesn't the Assert(!IsA(node, SubLink)) therein fire? (AFAICS, the existing comment is correct: we don't use this function

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-02-27 Thread Noah Misch
On Thu, Feb 27, 2014 at 05:33:47PM -0500, Tom Lane wrote: Noah Misch n...@leadboat.com writes: If the attached patch version looks reasonable, I will commit it. The test case is completely bogus, as the query explained is significantly different from the query executed. I'm not sure

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-02-27 Thread Kyotaro HORIGUCHI
Hello, At Thu, 27 Feb 2014 21:53:52 -0500, Noah Misch wrote On Thu, Feb 27, 2014 at 05:33:47PM -0500, Tom Lane wrote: Noah Misch n...@leadboat.com writes: If the attached patch version looks reasonable, I will commit it. The test case is completely bogus, as the query explained is

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-02-27 Thread Kyotaro HORIGUCHI
Sorry, I did wrong test. Noah Misch n...@leadboat.com writes: If the attached patch version looks reasonable, I will commit it. The test case is completely bogus, as the query explained is significantly different from the query executed. I'm not sure whether you can just

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-02-26 Thread Noah Misch
On Mon, Feb 03, 2014 at 07:36:22PM +0900, Kyotaro HORIGUCHI wrote: create table parent (a int, b int); create table child () inherits (parent); insert into parent values (1,10); insert into child values (2,20); select a, b from parent union all select a, b from child; Mmm. I

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-02-03 Thread Kyotaro HORIGUCHI
Hello, The attached file unionall_inh_idx_typ3_v6_20140203.patch is the new version of this patch fixed the 'whole-row-var' bug. First of all, on second thought about this, create table parent (a int, b int); create table child () inherits (parent); insert into parent values (1,10);

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-01-31 Thread Kyotaro HORIGUCHI
Hello, No hurry. Thanks. The attached two patches are rebased to current 9.4dev HEAD and make check at the topmost directory and src/test/isolation are passed without error. One bug was found and fixed on the way. It was an assertion failure caused by probably unexpected type

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-01-28 Thread Kyotaro HORIGUCHI
Hello, Thank you, and I' sorry to have kept you waiting. Let's focus on type 3; Tom and I both found it most promising. Agreed. The attached two patches are rebased to current 9.4dev HEAD and make check at the topmost directory and src/test/isolation are passed without error. One bug was

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-01-28 Thread Noah Misch
On Tue, Jan 28, 2014 at 06:53:32PM +0900, Kyotaro HORIGUCHI wrote: Hello, Thank you, and I' sorry to have kept you waiting. No hurry. The attached two patches are rebased to current 9.4dev HEAD and make check at the topmost directory and src/test/isolation are passed without error. One

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-01-24 Thread Noah Misch
On Tue, Jan 14, 2014 at 06:04:47PM +0900, Kyotaro HORIGUCHI wrote: I proposed 3 types of solution but the one of them - tweaking Equivalence Class (Type 1)- was dismissed because of inappropriate manipulation on Equivalence Class. The rest do essentially the same thing - flattening appendrels

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2014-01-14 Thread Kyotaro HORIGUCHI
This is cont'd from previous CF3. You'll see the overview and the discussion since in the thread begins from there. The issue ramains as of current 9.4dev head. http://www.postgresql.org/message-id/20131024.193953.233464126.horiguchi.kyot...@lab.ntt.co.jp The issue in brief is that UNION ALL on

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2013-11-26 Thread Tom Lane
Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp writes: My PATCH-1 which make them in the third group forcibly classified as 'parent' instead of 'child' as before therefore makes 'broken' ec member? If you marked child entries as non-child, then yes, it's broken. The definition of a

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2013-11-25 Thread Kyotaro HORIGUCHI
Thank you. I'll soon come up with regress for the patch 3, 4. = See commit dd4134ea, which added that text. IIRC, the key point is that among real EC members, there will never be duplicates: a Var x.y for instance cannot appear in two different ECs, because we'd have merged the two

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2013-11-25 Thread Kyotaro HORIGUCHI
Hello, I'll soon come up with regress for the patch 3, 4. New version patches added with regression tests are attached. I'll show you pulling-up-in-expand_inherited_rtentry version later. regards, -- Kyotaro Horiguchi NTT Open Source Software Center diff --git

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2013-11-24 Thread Noah Misch
On Sat, Nov 23, 2013 at 01:35:32PM -0500, Tom Lane wrote: Noah Misch n...@leadboat.com writes: I'm unclear on the key ideas behind distinguishing em_is_child members from ordinary EC members. src/backend/optimizer/README says These members are *not* full-fledged members of the

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2013-11-23 Thread Tom Lane
Noah Misch n...@leadboat.com writes: I'm unclear on the key ideas behind distinguishing em_is_child members from ordinary EC members. src/backend/optimizer/README says These members are *not* full-fledged members of the EquivalenceClass and do not affect the class's overall properties at all.

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2013-11-22 Thread Noah Misch
On Thu, Oct 24, 2013 at 07:39:53PM +0900, Kyotaro HORIGUCHI wrote: 1. Observed symptom As you know, UNION ALL accompanied with ORDER BY uses indexes if available. So do simple queries on partitioned (inheritance) tables. Nevertheless, UNION ALL on partitioned tables doesn't. This is

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2013-11-13 Thread Kyotaro HORIGUCHI
Umm. I might be working on a bit unstable place.. Your patch doesn't apply anymore. Please rebase it. Thank you. I rebased all patches to current master. regards, -- Kyotaro Horiguchi NTT Open Source Software Center diff --git a/src/backend/optimizer/path/equivclass.c

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2013-11-06 Thread Peter Eisentraut
On 10/29/13, 11:05 PM, Kyotaro HORIGUCHI wrote: Hello, Please add your patches to the currently-open CommitFest so that we don't lose track of them. https://commitfest.postgresql.org/action/commitfest_view/open I'm not sure which approach to this problem is best, but I agree that it is

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2013-10-29 Thread Kyotaro HORIGUCHI
Hello, Please add your patches to the currently-open CommitFest so that we don't lose track of them. https://commitfest.postgresql.org/action/commitfest_view/open I'm not sure which approach to this problem is best, but I agree that it is worth solving. Thank you, I've regsitered this

Re: [HACKERS] UNION ALL on partitioned tables won't use indices.

2013-10-25 Thread Robert Haas
On Thu, Oct 24, 2013 at 6:39 AM, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Hello, Sorry that it's been a while.. 1. Observed symptom As you know, UNION ALL accompanied with ORDER BY uses indexes if available. uniontest=# EXPLAIN SELECT * FROM c11 UNION ALL SELECT * FROM c12