Autovacuum (analyze) on partitioned tables for ATTACH/DETACH/DROP commands

in pgstat_recv_anl_ancestors. Attach the v1 patch. What do you think? [1] https://www.postgresql.org/message-id/ce5c3f04-fc17-7139-fffc-037f2c981bec%40enterprisedb.com -- Best regards, Yuzuko Hosoya NTT Open Source Software Center v1_autovacuum_for_attach_detach_drop_commands.patch Description

Re: Feedback on table expansion hook (including patch)

thub.com/HypoPG/hypopg/tree/master -- Best regards, Yuzuko Hosoya NTT Open Source Software Center

Re: pgsql: autovacuum: handle analyze for partitioned tables

andle it, let's discuss it, > but *I* don't see it. > I started thinking about the way to handle ATTACH/DETACH/DROP, but I haven't created patches. If no one has done it yet, I'll keep working. -- Best regards, Yuzuko Hosoya NTT Open Source Software Center

Re: Autovacuum on partitioned table (autoanalyze)

was not analyzed at the same timing as its leaf partitions due to the delay of propagating counters. According to your proposal, I added a separate loop to propagate the counters before collecting a list of relations to vacuum/analyze. -- Best regards, Yuzuko Hosoya NTT Open Source Software Center v15_autovacuum_on_partitioned_table.patch Description: Binary data

Re: Autovacuum on partitioned table (autoanalyze)

e should support only declarative partitioning in this patch for now, but what do you think? I'm not sure but if we can solve this matter at low cost by using the shared memory stats patch, should we wait for the patch? -- Best regards, Yuzuko Hosoya NTT Open Source Software Center

Re: Autovacuum on partitioned table (autoanalyze)

led this case yet, but I'll give it a try soon. Attach the v13 patch to this email. Could you please check it again? -- Best regards, Yuzuko Hosoya NTT Open Source Software Center v13_autovacuum_on_partitioned_table.patch Description: Binary data

Re: Release SPI plans for referential integrity with DISCARD ALL

it is no longer needed, so I think we can use this patch as a provisional solution. Any thoughts? [1] https://www.postgresql.org/message-id/flat/CA%2BHiwqGkfJfYdeq5vHPh6eqPKjSbfpDDY%2Bj-kXYFePQedtSLeg%40mail.gmail.com -- Best regards, Yuzuko Hosoya NTT Open Source Software Center v2_discard_ri

Re: Release SPI plans for referential integrity with DISCARD ALL

%2BHiwqG1qQuBwApueaUfA855UJ4TiSgFkPF34hQDWx3tOChV5w%40mail.gmail.com -- Best regards, Yuzuko Hosoya NTT Open Source Software Center

Re: Release SPI plans for referential integrity with DISCARD ALL

Hi Corey, Thank you for sharing. > Amit's patch is now available in this thread [1]. I'm curious if it has any > effect on your memory pressure issue. > I just found that thread. I'll check the patch. -- Best regards, Yuzuko Hosoya NTT Open Source Software Center

Release SPI plans for referential integrity with DISCARD ALL

thoughts? [1] https://www.postgresql.org/message-id/flat/cab4b85d-9292-967d-adf2-be0d803c3e23%40nttcom.co.jp_1 [2]https://www.postgresql.org/message-id/cab4b85d-9292-967d-adf2-be0d803c3e23%40nttcom.co.jp_1 -- Best regards, Yuzuko Hosoya NTT Open Source Software Center v1_discard_ri_spi_plans.patch

Re: Autovacuum on partitioned table (autoanalyze)

Hello Alvaro, On Thu, Dec 3, 2020 at 10:28 PM Alvaro Herrera wrote: > > Hello Yuzuko, > > On 2020-Dec-02, yuzuko wrote: > > > The problem Horiguchi-san mentioned is as follows: > > [explanation] > > Hmm, I see. So the problem is that if some ancestor is anal

Re: Autovacuum on partitioned table (autoanalyze)

reasonable but I'm not > > sure that doesn't break anything. > > I agree that showing NULLs for the vacuum columns is better. Perhaps > the most reasonable way to do this is use -1 as an indicator that NULL > ought to be returned from pg_stat_get_vacuum_count() et al, and add a > boolean in PgStat_TableCounts next to t_truncated, maybe "t_nullvacuum" > that says to store -1 instead of 0 in pgstat_recv_tabstat. > Thank you for the advice. I'll fix it based on this idea. -- Best regards, Yuzuko Hosoya NTT Open Source Software Center

Re: Autovacuum on partitioned table (autoanalyze)

unt| 0 > I haven't modified this part yet, but you meant that we should set null to counters about vacuum because partitioned tables are not vacuumed? -- Best regards, Yuzuko Hosoya NTT Open Source Software Center

Re: Autovacuum on partitioned table (autoanalyze)

Horiguchi-san, Thank you for your comments. On Fri, Oct 23, 2020 at 8:23 PM Kyotaro Horiguchi wrote: > > Thanks you for the new version. > > At Fri, 23 Oct 2020 15:12:51 +0900, yuzuko wrote in > > Hello, > > > > I reconsidered a way based on the v5 patch in

Re: Autovacuum on partitioned table (autoanalyze)

patch to this email. Could you check it again please? -- Best regards, Yuzuko Hosoya NTT Open Source Software Center v10_autovacuum_on_partitioned_table.patch Description: Binary data

Re: Autovacuum on partitioned table (autoanalyze)

n? > > > > This version now pass the tests in the Travis pipeline as can be seen in the > > link below, and is ready to be reviewed in the upcoming commitfest: > > > > http://cfbot.cputube.org/yuzuko-hosoya.html > > At Mon, 6 Jul 2020 19:35:37 +0900, yuz

Re: Autovacuum on partitioned table (autoanalyze)

atest patch that solves the above Werror. Could you please check it again? -- Best regards, Yuzuko Hosoya NTT Open Source Software Center v9_autovacuum_on_partitioned_table.patch Description: Binary data

Re: Autovacuum on partitioned table (autoanalyze)

> On Wed, Jul 1, 2020 at 6:26 PM Daniel Gustafsson wrote: > > > On 21 Apr 2020, at 18:21, yuzuko wrote: > > > I'll update the patch soon. > > Do you have an updated version to submit? The previous patch no longer > applies > to HEAD, so I'm marking this entry

Re: Autovacuum on partitioned table (autoanalyze)

Hello, On Sat, Apr 18, 2020 at 2:08 PM Justin Pryzby wrote: > > On Fri, Apr 17, 2020 at 10:09:07PM +0900, Amit Langote wrote: > > On Thu, Apr 16, 2020 at 11:19 PM Justin Pryzby wrote: > > > On Thu, Apr 16, 2020 at 06:16:45PM +0900, yuzuko wrote: > > > I don't think

Re: Autovacuum on partitioned table (autoanalyze)

to partitioning strategies and other requirements. So I think this patch can solve problem you mentioned. -- Best regards, Yuzuko Hosoya NTT Open Source Software Center

Re: Autovacuum on partitioned table

_tuples are updated when commiting transactions (at AtEOXact_PgStat) according to the number of inserted/updated/ deleted tuples of leaf partitions. Attach the latest patch. What do you think? -- Best regards, Yuzuko Hosoya NTT Open Source Software Center v7_autovacuum_on_partitioned_table.patch Description: Binary data

Re: Autovacuum on partitioned table

- > relname | p_2 > n_mod_since_analyze | 0 > > > I think if we analyze partition tree in order from leaf partitions > to root table, this problem can be fixed. > What do you think about it? > Attach the new patch fixes the above problem. Also, This patch includes modifications accoring to all comments Alvaro and Amit mentioned before in this thread. -- Best regards, Yuzuko Hosoya NTT Open Source Software Center v6_autovacuum_on_partitioned_table.patch Description: Binary data

Re: Autovacuum on partitioned table

-- relname | p_1 n_mod_since_analyze | 0 -[ RECORD 3 ]---+-- relname | p_2 n_mod_since_analyze | 0 I think if we analyze partition tree in order from leaf partitions to root table, this problem can be fixed. What do you think about it? -- Best regards, Yuzuko Hosoya NTT Open Source Software Center

Re: Autovacuum on partitioned table

) always reports 0 as msg.m_live_tuples and m_dead_tuples when the relation is partitioned. -- Best regards, Yuzuko Hosoya NTT Open Source Software Center v5_autovacuum_on_partitioned_table.patch Description: Binary data

Re: Autovacuum on partitioned table

ported only when Autovacuum process. So I fixed it too. -- Best regards, Yuzuko Hosoya NTT Open Source Software Center v4_autovacuum_on_partitioned_table.patch Description: Binary data

Re: Autovacuum on partitioned table

a time (from > bottom of the tree) or update all parents up to the root, every time a > leaf partition is analyzed. For multi-level partitioning, all parents' changes_since_analyze will be updated whenever analyzing a leaf partition in this patch. Could you please check the patch again? -- Best

Re: Autovacuum on partitioned table

bers related to (auto) vacuum need not be contained in the structure. I'm still writing a patch. I'll send it this week. -- Best regards, Yuzuko Hosoya NTT Open Source Software Center

Re: Autovacuum on partitioned table

of 'autovacuum_enabled' for partitioned tables might be false. Because autovacuum on *partitioned tables* requires scanning all children to make partitioned tables' statistics. But if the default value varies according to the relation, is it confusing? Any thoughts? -- Best regards, Yuzuko Hosoya NTT Open Source

Re: Autovacuum on partitioned table

Hi Laurenz, Thanks for the comments. On Mon, Dec 2, 2019 at 6:19 PM Laurenz Albe wrote: > > On Mon, 2019-12-02 at 18:02 +0900, yuzuko wrote: > > Greg reported in [1] before, autovacuum ignores partitioned tables. > > That is, even if individual partitions’ statistics are upd

Re: Partitioning versus autovacuum

(actual time=0.103..0.104 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on q (cost=0.00..1.01 rows=1 width=4) (actual time=0.072..0.074 rows=1 loops=1) Planning Time: 0.835 ms Execution Time: 3.310 ms (14 rows) -- Best regards, Yuzuko Hosoya NTT Open Source Software Center

Autovacuum on partitioned table

%2BhkB5OUcPtvr_U_EiSOjByoa-e4Q%40mail.gmail.com [2] https://www.postgresql.org/message-id/CA%2BHiwqEeZQ-H2OVbHZ%3Dn2RNNPF84Hygi1HC-MDwC-VnBjpA1%3DQ%40mail.gmail.com -- Best regards, Yuzuko Hosoya NTT Open Source Software Center v1_autovacuum_on_partitioned_table.patch Description: Binary data

Re: Problem with default partition pruning

atch Amit proposed. So I think this patch has no effect such as Simon's concern. I looked at Amit's patches and found it worked correctly. -- Best regards, Yuzuko Hosoya NTT Open Source Software Center

Re: Problem with default partition pruning

to get this done as the patch I posted in > https://postgr.es/m/20190806133053.GA23706@alvherre.pgsql > I agree with your proposal. Also, I confirmed a default partition was pruned as expected with your patch. -- Best regards, Yuzuko Hosoya NTT Open Source Software Center

Re: Problem with default partition pruning

cf%40lab.ntt.co.jp Best regards, Yuzuko Hosoya NTT Open Source Software Center On Mon, Aug 5, 2019 at 11:03 AM Alvaro Herrera wrote: > > I propose the comment rewordings as attached. Mostly, this updates the > comment atop the function to cover the case being modified, and then the >

Re: Problem with default partition pruning

Hi Alvaro and Amit, Thanks for reviewing and fixing the patch. Also, I confirmed the commit message explained the modification clearly. Thanks a lot. Yuzuko Hosoya On Mon, Aug 5, 2019 at 12:24 AM Alvaro Herrera wrote: > > On 2019-Aug-04, Alvaro Herrera wrote: > > > So this is

Re: Problem with default partition pruning

Hello, On Tue, Jun 25, 2019 at 1:45 PM yuzuko wrote: > > Hello Shawn, Alvaro, > > Thank you for testing patches and comments. > Yes, there are two patches: > (1) v4_default_partition_pruning.patch fixes problems with default > parti

Re: Problem with default partition pruning

-partitioned table's partition constraint. I'll post two patches together next time. Anyway, I'll rebase two patches to apply on master and fix space. Regards, Yuzuko Hosoya On Mon, Jun 24, 2019 at 12:45 PM Alvaro Herrera wrote: > > On 2019-Jun-24, shawn wang wrote: > > Hello, >

Runtime pruning problem

tually since "never executed" was displayed in the plan using EXPLAIN ANALYZE. But I think "One-Time Filter: false" and "Subplans Removed: ALL" or something like that should be displayed instead. What do you think? Best regards, Yuzuko Hosoya NTT Open Source Software Center

Re: Problem with default partition pruning

fujitsu.com; pgsql-hackers@lists.postgresql.org > Subject: Re: Problem with default partition pruning > > Hi. > > At Tue, 9 Apr 2019 16:41:47 +0900, "Yuzuko Hosoya" > wrote in > <00cf01d4eea7$afa43370$0eec9a50$@lab.ntt.co.jp> > > > So still it is wron

RE: Problem with default partition pruning

es. Specifically just after the for loop "for (i = > > 0 ; i < part_scheme->partnattrs; i++)". > I think we should check whether WHERE clause contradicts partition constraint even when the clause matches part attributes. So I move

RE: Problem with default partition pruning

Amit-san, > -Original Message- > From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] > Sent: Friday, April 05, 2019 6:47 PM > To: Yuzuko Hosoya ; 'Thibaut' > ; 'Imai, > Yoshikazu' > Cc: 'PostgreSQL Hackers' > Subject: Re: Problem with default partition

RE: Problem with default partition pruning

h=36) Filter: (a = 2) (9 rows) - I think that the place of check contradiction process was wrong At ignore_contradictory_where_clauses_at_partprune_step.patch. So I fixed it. Attached the latest patches. Please check it again. Best regards, Yuzuko Hosoya v2_ignore_contradictory_where_clauses_at_partprune_step.patch Description: Binary data v4_default_partition_pruning.patch Description: Binary data

RE: Problem with default partition pruning

constraint, > fixing problems unearthed by Thibaut's tests I attached the latest patches according to Amit comment. v3_default_partition_pruning.patch fixes default partition pruning problems and ignore_contradictory_where_clauses_at_partprune_step.patch fixes sub-partition problems Thibaut teste

RE: Problem with default partition pruning

raint > is not loaded by the planner. Note that pruning is only used if a query > specifies the parent table, > not a partition. Thanks for the comments. I saw that email. Also, I checked that query Thibaut mentioned worked correctly with Amit's patch discussed in that thread. Best regards, Yuzuko Hosoya

RE: Problem with default partition pruning

where id = 25; QUERY PLAN ------- Append (cost=0.00..25.91 rows=6 width=36) -> Seq Scan on test2_10_20_def (cost=0.00..25.88 rows=6 width=36) Filter: (id = 25) As Amit described in the previous email, id = 25 contradicts test2_0_20's partition constraint, so I think this clause should be ignored and we can also handle this case in the similar way as Amit proposal. I attached v1-delta-2.patch which fix the above issue. What do you think about it? Best regards, Yuzuko Hosoya v1-delta-2.patch Description: Binary data

RE: Problem with default partition pruning

Hi Thibaut, Thanks a lot for your test and comments. > > Le 28/02/2019 à 09:26, Imai, Yoshikazu a écrit : > > Hosoya-san > > > > On Wed, Feb 27, 2019 at 6:51 AM, Yuzuko Hosoya wrote: > >>> From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] > >&

Re: Problem with default partition pruning

Hi Ibrar, On Tue, Mar 5, 2019 at 2:37 AM Ibrar Ahmed wrote: > > Hi Yuzuko Hosoya, > > Ignore my last message, I think this is also a legitimate scan on default > partition. > Oh, I got it. Thanks a lot. > > On Mon, Mar 4, 2019 at 10:29 PM Ibrar Ahmed wrote: >&g

Re: Problem with default partition pruning

Imai-san, Thanks for sharing your tests! On Thu, Feb 28, 2019 at 5:27 PM Imai, Yoshikazu wrote: > > Hosoya-san > > On Wed, Feb 27, 2019 at 6:51 AM, Yuzuko Hosoya wrote: > > > From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] > > > Sent: Wednesd

RE: Problem with default partition pruning

Amit-san, > From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] > Sent: Wednesday, February 27, 2019 11:22 AM > > Hosoya-san, > > On 2019/02/22 17:14, Yuzuko Hosoya wrote: > > Hi, > > > > I found the bug of default partition pruning when executing a ran

Problem with default partition pruning

atching_range_bounds() determines only offsets of range bounds according to each condition - These results are combined at perform_pruning_combine_step() - Whether the default partition is scanned or not is determined at get_matching_partitions() Attached the patch. Any feedback is greatly appre

RE: Improve selectivity estimate for range queries

pain point. So I tried distinguishing explicitly between real estimates and otherwise as Robert said. The idea Tom proposed and Horiguchi-san tried seems reasonable, but I'm concerned whether any range queries really cannot match 0.342 (or some such) by accident in any environments. Is the way which Horiguchi-san did enough to prove that? Best regards, Yuzuko Hosoya NTT Open Source Software Center

RE: Improve selectivity estimate for range queries

Hi, Thanks for the comments. I attach the v2 patch. > From: Kyotaro HORIGUCHI [mailto:horiguchi.kyot...@lab.ntt.co.jp] > Sent: Friday, December 21, 2018 12:25 PM > > Hello. > > At Thu, 20 Dec 2018 17:21:29 +0900, "Yuzuko Hosoya" > wrote in > <008701d4

Improve selectivity estimate for range queries

ghts? [1] https://git.postgresql.org/gitweb/?p=postgresql.git=commitdiff=4c2777d0b733220d9029f78817af8ce Best regards, Yuzuko Hosoya NTT Open Source Software Center improve_selectivity_estimate_for_range_queries.patch Description: Binary data

RE: A typo in partprune.c

Hi Michael, > From: Michael Paquier [mailto:mich...@paquier.xyz] > Sent: Thursday, November 08, 2018 8:17 PM > > On Thu, Nov 08, 2018 at 07:19:14PM +0900, Yuzuko Hosoya wrote: > > Here is the patch to fix it. > > Thanks, committed. Thank you. Yuzuko Hosoya NTT Open Source Software Center

A typo in partprune.c

is the patch to fix it. Best regards, Yuzuko Hosoya NTT Open Source Software Center fix_partprune_typo.patch Description: Binary data