Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-11-14 Thread David Rowley
On 15 November 2017 at 04:23, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > David Rowley wrote: >> I'd have thought some sort of: ALTER INDEX name_of_partitioned_index >> REPLACE INDEX FOR partitioned_tablename WITH >> name_of_new_matching_bloat_free_index; >>

Re: [HACKERS] Runtime Partition Pruning

2017-11-14 Thread David Rowley
On 15 November 2017 at 01:57, David Rowley <david.row...@2ndquadrant.com> wrote: > I think to do this you're going to have to store some sort of array > that maps the partition index to the subpath in the Append node so you > can correctly identify the subpath based on what you'

Re: [HACKERS] Partition-wise aggregation/grouping

2017-11-15 Thread David Rowley
ff, but please, let's not talk about it here. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] path toward faster partition pruning

2017-11-15 Thread David Rowley
r loop) I see similar here with __attribute((noinline)). Thanks for investigating that. Your way is clearly better. Thanks for suggesting it. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Removing [Merge]Append nodes which contain a single subpath

2017-11-29 Thread David Rowley
On 30 November 2017 at 15:34, Michael Paquier <michael.paqu...@gmail.com> wrote: > On Wed, Nov 15, 2017 at 3:17 PM, David Rowley > <david.row...@2ndquadrant.com> wrote: > > The remove_singleton_appends_examples_of_differences_2017-11-15.patch > > which

Re: [HACKERS] Removing LEFT JOINs in more cases

2017-11-29 Thread David Rowley
also one to ensure non-RTE_RELATION relations can be removed too. Updated patch attached. Thanks again for the review. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services remove_left_join_distinct_2017-11-30.patch Description: Binary data

Re: [HACKERS] Removing LEFT JOINs in more cases

2017-11-30 Thread David Rowley
Great. Many thanks for the review! -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Combine function returning NULL unhandled?

2017-11-30 Thread David Rowley
u for making the fix. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-11-30 Thread David Rowley
e an index which is used by a constraint, which to me seems like a feature we should have had years ago. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-12-04 Thread David Rowley
s A+B. Okay, I wasn't insisting, just asking if you thought this was missing from the patch. However, I do still feel that if we're adding an index to an object then it should be available in RelOptInfo->indexlist, but this patch is still good progress even if we don't add it there. -- David

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-12-04 Thread David Rowley
On 2 December 2017 at 03:39, Robert Haas <robertmh...@gmail.com> wrote: > On Thu, Nov 30, 2017 at 11:39 PM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> I feel like we could do better here with little extra effort. The >> DETACH index feature does not reall

Parallel Aggregates for string_agg and array_agg

2017-12-17 Thread David Rowley
about this is seeing about getting rid of some of the code duplication between array_agg_array_combine and accumArrayResultArr. I'm going to add this to PG11's final commitfest rather than the January 'fest as it seems more like a final commitfest type of patch. -- David Rowley

Re: pg_(total_)relation_size and partitioned tables

2017-12-17 Thread David Rowley
e between a tuple and a row. A relation is just what we use to implement tables, and there can be multiple relations per table (in the partitioning case), similar to how there can be multiple tuple versions for a single row. So that might back up that pg_table_size should include all relations

Re: Why does array_position_common bitwise NOT an Oid type?

2017-12-17 Thread David Rowley
On 17 December 2017 at 14:53, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Rowley <david.row...@2ndquadrant.com> writes: >> I was puzzled to see the following code: > >> my_extra->element_type = ~element_type; > > If memory serves, the idea was to forc

Re: [HACKERS] path toward faster partition pruning

2017-12-13 Thread David Rowley
contains all append rels; ignore others */ if (appinfo->parent_relid != parentRTindex) continue; -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Runtime Partition Pruning

2017-12-15 Thread David Rowley
Please find attached my patch, which is based directly on top of Amit's faster partition pruning v14 [1], which I patched against 4034db215b9 [1] https://www.postgresql.org/message-id/9b98fc47-34b8-0ab6-27fc-c8a0889f2e5b%40lab.ntt.co.jp -- David Rowley http://www.2ndQuadran

Small typo in comment in json_agg_transfn

2017-12-17 Thread David Rowley
The attached fixed a small typo in json_agg_transfn. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services json_agg_transfn_comment_fix.patch Description: Binary data

Re: [HACKERS] Runtime Partition Pruning

2017-12-18 Thread David Rowley
On 18 December 2017 at 21:31, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > On 2017/12/16 15:05, David Rowley wrote: >> I've been looking over this and I think that the use of the >> PartitionDispatch in set_append_subplan_indexes is not correct. What >>

Re: [HACKERS] path toward faster partition pruning

2017-12-18 Thread David Rowley
resulting set could end up with a few more trailing 0 words than what you have now, but it to be a better idea not allocate a new set each time. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] path toward faster partition pruning

2017-12-18 Thread David Rowley
On 19 December 2017 at 17:36, David Rowley <david.row...@2ndquadrant.com> wrote: > Also, instead of using bms_intersect here, would it be better to do: > > result = bms_del_members(result, or_partset); ? I should have said bms_int_members() rather than bms_del_members() --

Re: Notes about Pl/PgSQL assignment performance

2017-12-19 Thread David Rowley
ith multiple instances running. Perhaps something in there might appear in the samples more often with the multiple instances than it does with a single instance. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] path toward faster partition pruning

2017-12-19 Thread David Rowley
On 19 December 2017 at 17:36, David Rowley <david.row...@2ndquadrant.com> wrote: > I'm sorry to say this is another micro review per code I'm stumbling > over when looking at the run-time partition pruning stuff. Again, another micro review. I apologise for the slow trickle of r

Re: [HACKERS] path toward faster partition pruning

2017-12-18 Thread David Rowley
over to this being needed. What do you think? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-12-17 Thread David Rowley
NDEX for the partitioned index. Does that go and create each leaf partition index regardless of if there is a suitable candidate to ATTACH? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Why does array_position_common bitwise NOT an Oid type?

2017-12-16 Thread David Rowley
een cached, but then why would it not use InvalidOid for that? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-12-17 Thread David Rowley
On 18 December 2017 at 15:04, Robert Haas <robertmh...@gmail.com> wrote: > On Sun, Dec 17, 2017 at 5:29 AM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> I'm now not that clear on what the behaviour is if the ONLY keyword is >> not specified on the CREATE I

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-12-17 Thread David Rowley
On 18 December 2017 at 15:59, Robert Haas <robertmh...@gmail.com> wrote: > On Sun, Dec 17, 2017 at 9:38 PM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> On 18 December 2017 at 15:04, Robert Haas <robertmh...@gmail.com> wrote: >>> On Sun

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-11-17 Thread David Rowley
too. What do you think? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-11-17 Thread David Rowley
); I see it ends up making use of the hash index on p2 to support the index that's stored as a btree on the partitioned table. I think these should match so that the operations we can perform on the index are all aligned. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL De

Re: [HACKERS] path toward faster partition pruning

2017-11-17 Thread David Rowley
king those changes and adding the HASH partition support. There's a good chance that I'm not going to get time to look at this maybe until the last day of the month. I hope someone else can look over it in the meantime. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Develop

Re: Further simplification of c.h's #include section

2017-11-16 Thread David Rowley
On 16 November 2017 at 19:14, Michael Paquier <michael.paqu...@gmail.com> wrote: > On Thu, Nov 16, 2017 at 2:23 PM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> I do get some warnings: >> >> (ClCompile target) -> >> src/test/module

Re: [HACKERS] No mention of CREATE STATISTICS in event trigger docs

2017-11-13 Thread David Rowley
On 14 November 2017 at 07:39, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > David Rowley wrote: >> A patch to fix this is attached. > > Thanks, pushed. Thanks for pushing. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 2

Re: [HACKERS] path toward faster partition pruning

2017-11-13 Thread David Rowley
111 1111 $ clang --version clang version 4.0.1-6 (tags/RELEASE_401/final) Target: x86_64-pc-linux-gnu Thread model:

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-11-14 Thread David Rowley
ne manually edited the pg_dump file to remove the CREATE INDEX statement for the leaf partition, and if they do that, then maybe they won't be so surprised that CREATE INDEX has to create some indexes. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-11-14 Thread David Rowley
On 15 November 2017 at 01:30, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > David Rowley wrote: >> hmm, but surely the all those indexes must already exist if the >> partitioned index exists. Won't we be disallowing DROP INDEX of the >> leaf partition indexes if th

Re: [HACKERS] Runtime Partition Pruning

2017-11-14 Thread David Rowley
're getting back from get_partitions_for_keys(). Perhaps what you had worked previously when we were not returning a Bitmapset with that function. Once you've got that design worked out I can take another look at this. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Deve

Re: [HACKERS] UPDATE of partition key

2017-11-13 Thread David Rowley
On 14 November 2017 at 01:55, David Rowley <david.row...@2ndquadrant.com> wrote: > I'll try to continue with the review tomorrow, but I think some other > reviews are also looming too. I started looking at this again today. Here's the remainder of my review. 29. ExecSetupChildPa

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-11-14 Thread David Rowley
ioned index is created on the parent. I've not read the patch yet, but reading the thread it does not seem to have gone in this direction. Maybe I'm overlooking something? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Further simplification of c.h's #include section

2017-11-15 Thread David Rowley
ng C4113: 'void (__cdecl *)()' differs in parameter lists from 'session_end_hook_type' [test_session_hooks.vcxproj]. 2 Warning(s) But I imagine that was caused by 745948422c (also get the warnings without your patch) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL De

Re: Treating work_mem as a shared resource (Was: Parallel Hash take II)

2017-11-15 Thread David Rowley
execution start waiting for other plans to complete. That might not be ideal, as we'd be assuming that a plan will always consume all their work_mems at once, but it seems better than what we have today. Maybe we can improve on it later. -- David Rowley http://www.2ndQuadrant.com

Re: [HACKERS] Runtime Partition Pruning

2017-12-07 Thread David Rowley
join_clauses); + It just does not due to the node->join_clauses being NULL. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-12-07 Thread David Rowley
used. I'm just not that clear on if that column should be set to the leaf partition's direct parent, or the parent that the CREATE INDEX was done on. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-12-07 Thread David Rowley
On 8 December 2017 at 10:51, Robert Haas <robertmh...@gmail.com> wrote: > On Thu, Dec 7, 2017 at 4:43 PM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> And yeah, this does nothing for making sure we choose the correct >> index if more than one matching inde

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-12-07 Thread David Rowley
On 8 December 2017 at 11:23, Robert Haas <robertmh...@gmail.com> wrote: > On Thu, Dec 7, 2017 at 4:57 PM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> That's true, but is it worth inventing/maintaining an ATTACH syntax >> for that? It's not a very comm

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-12-05 Thread David Rowley
On 6 December 2017 at 04:29, Robert Haas <robertmh...@gmail.com> wrote: > On Mon, Dec 4, 2017 at 6:57 PM, David Rowley >> I proposed that this worked a different way in [1]. I think the way I >> mention is cleaner as it means there's no extra reason for a >> partiti

Out of date comment in cached_plan_cost

2017-12-07 Thread David Rowley
may be a more complex case. The attached is my attempt at putting this right. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services cached_plan_cost_comment_fix.patch Description: Binary data

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-12-05 Thread David Rowley
to keep them valid so that queries can actually use them for something. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-12-05 Thread David Rowley
On 6 December 2017 at 09:58, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > David Rowley wrote: >> On 6 December 2017 at 04:29, Robert Haas <robertmh...@gmail.com> wrote: >> > How does that proposal keep pg_dump from latching onto the wrong >> > index, if

Re: [HACKERS] Proposal: Local indexes for partitioned table

2017-12-05 Thread David Rowley
On 6 December 2017 at 13:42, David Rowley <david.row...@2ndquadrant.com> wrote: > On 6 December 2017 at 11:35, Robert Haas <robertmh...@gmail.com> wrote: >> What are we giving up by explicitly attaching >> the correct index? > > The part I don't like about the

Re: [HACKERS] Runtime Partition Pruning

2017-12-07 Thread David Rowley
On 7 December 2017 at 20:22, Beena Emerson <memissemer...@gmail.com> wrote: > PFA the updated patch. Hi Beena, Thanks for updating this. Can you list the patches which are required for this to apply to today's master branch? -- David Rowley http://www.2ndQua

Re: [HACKERS] path toward faster partition pruning

2017-12-07 Thread David Rowley
13 0005 patch no longer applies to current master. Are you working on splitting this up as requested by Robert above? I can continue reviewing this once patches are available that apply to current master. Many thanks -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Out of date comment in cached_plan_cost

2017-12-11 Thread David Rowley
nd too much effort rewording a comment. My vote is for the original patch I sent. I only changed it because Robert complained that technically an inheritance child could actually be a partition. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Removing [Merge]Append nodes which contain a single subpath

2017-12-11 Thread David Rowley
On 11 December 2017 at 21:18, Ashutosh Bapat <ashutosh.ba...@enterprisedb.com> wrote: > On Thu, Dec 7, 2017 at 5:11 AM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> While rebasing this today I also noticed that we won't properly detect >> unique joins in a

Re: [HACKERS] Removing [Merge]Append nodes which contain a single subpath

2017-12-11 Thread David Rowley
r to how I'm propagating the PathKeys for the same case in this patch, that way the unique join code would find the UniqueKeys instead of what it does today and not find any unique indexes on the partitioned table. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development

Re: [HACKERS] Runtime Partition Pruning

2017-12-05 Thread David Rowley
e so that we can tell that partition pruning is in use. > I'm not sure if that is as simple as "Run-Time Partition Pruning: Yes" > or if we can give a few more useful details. It already does. Anything subnode with "(never executed)" was pruned at runtime. Do we really need

Re: Accessing base table relational names via RelOptInfo

2017-12-06 Thread David Rowley
h is the Oid of the relation. You can then call get_rel_name() on that Oid. You'll also need to ensure the relid actually belongs to a relation, for this check that the RangeTblEntry->rtekind is RTE_RELATION. You might also want to think about schema names since two relations can share the sam

Re: Out of date comment in cached_plan_cost

2017-12-10 Thread David Rowley
On 9 December 2017 at 06:05, Robert Haas <robertmh...@gmail.com> wrote: > On Thu, Dec 7, 2017 at 3:14 AM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> The attached is my attempt at putting this right. > > I don't feel entirely right about the way thi

Re: [HACKERS] Runtime Partition Pruning

2017-12-21 Thread David Rowley
version of the patch. This is based on Amit's v15 of faster-partition-pruning [1] which I found to cleanly apply to f94eec490 [1] https://www.postgresql.org/message-id/06cde8a5-0ac7-dcf5-ad66-1ca781623...@lab.ntt.co.jp -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services runtime_prune_drowley_v2.patch Description: Binary data

Re: [HACKERS] path toward faster partition pruning

2017-12-21 Thread David Rowley
ble why this can't work for RANGE. HASH is also understandable since we don't have a complete picture of all the values that can be contained within the partition. I'll try to do another complete review of v16 soon. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Runtime Partition Pruning

2017-12-21 Thread David Rowley
On 21 December 2017 at 23:51, Beena Emerson <memissemer...@gmail.com> wrote: > On Thu, Dec 21, 2017 at 2:31 PM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> On 19 December 2017 at 21:54, Beena Emerson <memissemer...@gmail.com> wrote: > &g

Re: [HACKERS] path toward faster partition pruning

2017-12-21 Thread David Rowley
AND (a <> 2)) (3 rows) This code appears to be at fault: /* * Also, exclude the "null-only" partition, because strict clauses in * ne_clauses will not select any rows from it. */ if (count_partition_datums(relation, boundinfo->null_index) == 0) excluded_parts = bms_add_membe

Re: [HACKERS] Runtime Partition Pruning

2017-12-21 Thread David Rowley
On 21 December 2017 at 22:01, David Rowley <david.row...@2ndquadrant.com> wrote: > I've attached the latest version of the patch. This is based > on Amit's v15 of faster-partition-pruning [1] which I found to cleanly > apply to f94eec490 Well, that went out of date pretty quickl

Re: Should we add GUCs to allow partition pruning to be disabled?

2018-05-07 Thread David Rowley
nces of "Append" in the docs. There were some, so I didn't think I was breaking any rules. I also have no idea how else we might explain that it works for Append and not MergeAppend. It's likely going to be easier to answer possible to future bug reports which complain run-time pruning is

Re: Should we add GUCs to allow partition pruning to be disabled?

2018-05-06 Thread David Rowley
th considering. For now, I think removing "actual" won't help. A patch will follow shortly, in response to Amit's review. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Needless additional partition check in INSERT?

2018-05-13 Thread David Rowley
check_partition_constr = (resultRelInfo->ri_PartitionCheck != NIL); I also noticed that. Apart from that, I think your version is correct, but I just don't think it's quite as easy to understand. Although that's certainly debatable. For now, I'll refrain from writing v4 unless there's some cons

Incorrect comment in get_partition_dispatch_recurse

2018-05-13 Thread David Rowley
o fix it by removing just that part, but instead, I ended up rewriting the whole thing. Patch attached. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services get_partition_dispatch_recurse_comment_fix.patch Description: Binary data

Re: Incorrect comment in get_partition_dispatch_recurse

2018-05-14 Thread David Rowley
On 14 May 2018 at 17:29, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > On 2018/05/14 13:57, David Rowley wrote: >> I noticed that a comment in get_partition_dispatch_recurse claims that: >> >> "it contains the >> * leaf partition's position in t

Re: Postgres 11 release notes

2018-05-15 Thread David Rowley
> I expect a torrent of feedback. ;-) I wonder if 3cda10f41bfed -- "Use atomic ops to hand out pages to scan in parallel scan." should be listed in the notes. If so, I'd suggest something like: Improve performance of Parallel Seq Scans with many parallel workers (David Rowley). I'

Re: Postgres 11 release notes

2018-05-15 Thread David Rowley
dded in float.c by Dang's patch (61b200e2f) was effectively reverted by 6bdf1303. Dang's regression tests remain, so should also be credited along with Tom. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] path toward faster partition pruning

2018-05-08 Thread David Rowley
d it introduced a make_partition_pruneinfo() which in turn > calls get_partprune_steps. Yeah. Likely left over from when run-time pruning was generating the steps during execution rather than during planning. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Should we add GUCs to allow partition pruning to be disabled?

2018-05-08 Thread David Rowley
Thanks for reviewing again. On 9 May 2018 at 01:32, Justin Pryzby <pry...@telsasoft.com> wrote: > On Mon, May 07, 2018 at 06:00:59PM +1200, David Rowley wrote: >> Many thanks for reviewing this. > > 2nd round - from the minimalist department: > > +partitions which

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

2018-04-27 Thread David Rowley
On 26 April 2018 at 21:03, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > I noticed also that the item regarding row triggers might be obsolete as > of 86f575948c7, thanks again to Alvaro! So, I updated your patch to take > care of that. Thanks. I walked right past that o

Re: Should we add GUCs to allow partition pruning to be disabled?

2018-05-09 Thread David Rowley
cribe exclusion > in terms of pruning. But... that's not true. The chapter describes inheritance partitioned tables too, and we're not getting rid of constraint exclusion because it's needed for those. However, that might not mean your patch has to be changed. I'd better have a look...

Re: Needless additional partition check in INSERT?

2018-05-10 Thread David Rowley
. In order to make it a bit easier to document I changed the way that check_partition_constr is set. This is now done with an if/else if/else clause for both COPY and INSERT. Hopefully, that's easier to understand and prevents further mistakes. Patch attached. -- David Rowley

Re: Needless additional partition check in INSERT?

2018-05-09 Thread David Rowley
On 10 May 2018 at 16:13, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > The patch to ExecInsert looks good, but I think we also need to do the > same thing in CopyFrom. I think so too. Updated patch attached. -- David Rowley http://www.2ndQuadrant.com/

Needless additional partition check in INSERT?

2018-05-09 Thread David Rowley
, providing there's some other constraint. ExecFindPartition should have already located the correct partition and nothing should have changed in the absence of before row insert triggers, so it looks like we're fine to not bother re-checking. Or did I misunderstand? -- David Rowley

Re: Should we add GUCs to allow partition pruning to be disabled?

2018-05-13 Thread David Rowley
On 13 May 2018 at 03:30, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > Amit Langote wrote: > >> +1 to this more radical overhaul of this part of the documentation. > > Thanks. I pushed now after some more tweaking, Thanks for pushing. -- David Rowley

Re: Should we add GUCs to allow partition pruning to be disabled?

2018-05-10 Thread David Rowley
quot;Subplans Removed" that's there today to "Subplans removed by run-time pruning" These names are not very good, also. I'm also not very excited about adding this. This also does nothing for phase 3. Would something like that address your concern? Or do you have another idea? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Needless additional partition check in INSERT?

2018-05-11 Thread David Rowley
ri_PartitionRoot is a Boolean. If this is some new coding rule, then that's the first I've heard of it. Scanning over the result of git grep -E "if \(!\w{1,}\)" it looks like we have a bit of cleanup work to do before we can comply. FWIW, I've previously been told off for the opposite.

Re: Incorrect comment in get_partition_dispatch_recurse

2018-05-16 Thread David Rowley
On 17 May 2018 at 02:51, Robert Haas <robertmh...@gmail.com> wrote: > On Mon, May 14, 2018 at 12:57 AM, David Rowley > <david.row...@2ndquadrant.com> wrote: >> The "minus 1" part is incorrect. It simply just stores the 0-based >> index of the item in the

Should total_pages be calculated after partition pruning and constraint exclusion?

2018-05-15 Thread David Rowley
until after set_base_rel_sizes() means it's still done in time before it's needed in set_base_rel_pathlists(). The attached patch implements the change. There are no visible plan changes in the regression tests, but the change can affect the plans for larger partitioned tables. -- David Rowley

Re: Postgres 11 release notes

2018-05-15 Thread David Rowley
On 16 May 2018 at 02:01, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Rowley <david.row...@2ndquadrant.com> writes: >> While I'm not in favour of removing Dang's credit here, technically >> this patch was Tom's. The code added in float.c by Dang's patch >> (61

Re: Removing unneeded self joins

2018-05-16 Thread David Rowley
haps a knob is a good first step, then maybe having the ability to set that knob to "automatic" is something to aspire for later. I don't think Alexander should work on this as part of this patch though. Perhaps we can re-evaluate when Alexander posts some planner benchmarks from the p

COPY FROM WITH HEADER skips a tuple every 4 billion tuples

2018-05-22 Thread David Rowley
186) A patch to fix is attached. (I just made the variable 64-bit) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services 0001-Fix-COPY-FROM-not-to-skip-a-tuple-every-2-32-tuples.patch Description: Binary data

Re: Postgres 11 release notes

2018-05-22 Thread David Rowley
On 23 May 2018 at 13:18, Bruce Momjian <br...@momjian.us> wrote: > On Mon, May 21, 2018 at 07:34:18PM +1200, David Rowley wrote: >> I've been working a bit in this area over the past few weeks and with >> PG11 I measured a single INSERT into a 10k RANGE partitioned ta

Re: Subplan result caching

2018-05-23 Thread David Rowley
did it this way, as a followup we could go plug it into parameterised nested loops to speed up repeated lookups of the inner side plan. The gains there are probably similar to what you've mentioned. What do you think? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Deve

Re: documentation fixes for partition pruning, round two

2018-05-23 Thread David Rowley
know if it's useful to provide a patch. > > I propose this. Thanks for working on this. Can you just attach the patch? Personally, for me, it's much easier to review when applied rather than looking at an email. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL

Re: Subplan result caching

2018-05-23 Thread David Rowley
uching the decorrelation as part of this effort. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: COPY FROM WITH HEADER skips a tuple every 4 billion tuples

2018-05-22 Thread David Rowley
e number in the COPY FROM input, the other tracks the number of rows inserted. You'd only have to add a BEFORE INSERT ROW trigger which blocks some rows to understand why they need to be separate. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: COPY FROM WITH HEADER skips a tuple every 4 billion tuples

2018-05-22 Thread David Rowley
2 vs 43. Did I misunderstand you? -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: COPY FROM WITH HEADER skips a tuple every 4 billion tuples

2018-05-22 Thread David Rowley
On 23 May 2018 at 09:16, Vik Fearing <vik.fear...@2ndquadrant.com> wrote: > I think Tom was wondering why it isn't showing 5032703. You'll need to explain that one. The number just looks like nonsense to me. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL De

Re: Incorrect comment in get_partition_dispatch_recurse

2018-05-17 Thread David Rowley
will just slow it down further. I have patches locally that I'll be submitting during the v12 cycle to improve on this. Among other things, the patches go to lengths to not allocate these arrays when we don't have to. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL De

Re: Incorrect comment in get_partition_dispatch_recurse

2018-05-17 Thread David Rowley
On 18 May 2018 at 06:21, Robert Haas <robertmh...@gmail.com> wrote: > All right, so let's just say that explicitly. Maybe something like > the attached. That looks fine to me. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Suppo

Re: Incorrect comment in get_partition_dispatch_recurse

2018-05-16 Thread David Rowley
*leaf_part_oids = lappend_oid(*leaf_part_oids, partrelid); > -pd->indexes[i] = list_length(*leaf_part_oids) - 1; > } > else > { That makes sense. It's probably less confusing that way. -- David Rowley http://www.

Re: partition -> partitioned

2018-05-16 Thread David Rowley
runeinfo called > 'partition_rels'. > > Attached a patch to make that uniform to avoid confusion. Looks good to me. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Removing unneeded self joins

2018-05-16 Thread David Rowley
On 17 May 2018 at 10:55, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Rowley <david.row...@2ndquadrant.com> writes: >> There's probably some argument for delaying obtaining the relation >> size until after join removal and probably partition pruning too, but >>

Re: Removing unneeded self joins

2018-05-16 Thread David Rowley
uld cause the planner to not work so well in face of the relation changing size significantly between analyze runs. FWIW the major case where that does show up is when generating a plan for a partitioned table with many partitions then pruning all but a few of them. -- David Rowley

Re: NaNs in numeric_power (was Re: Postgres 11 release notes)

2018-05-16 Thread David Rowley
On 16 May 2018 at 09:55, Tom Lane <t...@sss.pgh.pa.us> wrote: > David Rowley <david.row...@2ndquadrant.com> writes: >> On 16 May 2018 at 02:01, Tom Lane <t...@sss.pgh.pa.us> wrote: >>> I'm not particularly fussed about getting credit for that. However, >&g

Re: Removing unneeded self joins

2018-05-16 Thread David Rowley
oin removal and probably partition pruning too, but it's currently done well before that in build_simple_rel, where the RelOptInfo is built. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Removing unneeded self joins

2018-05-16 Thread David Rowley
in removal code does (you may need to do some extra work around equivalence classes)) -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

  1   2   3   4   5   6   7   >