RE: Run-time pruning for ModifyTable

On Monday, July 8, 2019 11:34 AM, Amit Langote wrote: > By the way, let's keep any further discussion on this particular topic > in the other thread. Thanks for the details. I got it. Regards, Kato sho > -Original Message- > From: Amit Langote [mailto:amitlangot...@

RE: Run-time pruning for ModifyTable

; to have to perform initial and execution-time pruning, respectively. Does this mean that the generic plan will not have N subplans for N result relations? I thought [1] would make creating generic plans faster, but is this correct? regards, kato sho > -Original Message- > From: Amit

RE: Why does not subquery pruning conditions inherit to parent query?

On Friday, May 31, 2019 9:33 PM, David Rowley wrote: > On Fri, 31 May 2019 at 03:18, Kato, Sho wrote: > > Is it possible to improve a subquery quals to pull up into outer query? > > Sure, it's possible, but it would require writing code. When it can and > cannot/should no

RE: Why does not subquery pruning conditions inherit to parent query?

t merged into the parent. Thank you for your replay and sorry for late response. Ok, I understand. Is it possible to improve a subquery quals to pull up into outer query? Oracle looks like do that. Regards, Kato Sho > -Original Message- > From: Tom Lane [mailto:t...@sss.pgh.pa.us]

RE: Why does not subquery pruning conditions inherit to parent query?

> From: David Rowley [mailto:david.row...@2ndquadrant.com] > Sent: Friday, May 24, 2019 5:10 PM > To: Kato, Sho/加藤 翔 > Cc: pgsql-hack...@postgresql.org > Subject: Re: Why does not subquery pruning conditions inherit to parent > query? > > On Fri, 24 May 2019 at 19:44, Kat

Why does not subquery pruning conditions inherit to parent query?

Hello I execute following query to the partitioned table, but the plan is different from my assumption, so please tell me the reason. postgres=# explain select * from jta, (select a, max(b) from jtb where a = 1 group by a ) c1 where jta.a = c1.a; QUERY PLAN

RE: Speeding up creating UPDATE/DELETE generic plan for partitioned table into a lot

is also less confident. If there is another good way, please let me know. regards, > -Original Message----- > From: Kato, Sho [mailto:kato-...@jp.fujitsu.com] > Sent: Wednesday, February 20, 2019 3:11 PM > To: Tsunakawa, Takayuki/綱川 貴之 ; > 'David Rowley' ; Amit Langote &

RE: Fix typo in test code comments

> Committed. Thanks! Regards, sho kato > -Original Message- > From: Michael Paquier [mailto:mich...@paquier.xyz] > Sent: Friday, March 15, 2019 4:24 PM > To: Kato, Sho/加藤 翔 > Cc: 'Kyotaro HORIGUCHI' ; > pgsql-hack...@postgresql.org > Subject: Re: Fix

RE: Fix typo in test code comments

x27;); Oops, thank you for your advice. I fixed it. Regards, Sho Kato > -Original Message- > From: Kyotaro HORIGUCHI [mailto:horiguchi.kyot...@lab.ntt.co.jp] > Sent: Friday, March 15, 2019 2:24 PM > To: Kato, Sho/加藤 翔 > Cc: pgsql-hack...@postgresql.org > Subject: Re: Fix

Fix typo in test code comments

Hi, I think ts_vector is a typo for tsvector. regards, Sho Kato fix-typo-for-tsvector.patch Description: fix-typo-for-tsvector.patch

Improve the generic plan mechanism

Hi, hackers I want to improve the generic plan mechanism and speed up the UPDATE/DELETE planning of a table partitioned into thousands. However, I am not sure if this is realistic, I would like advice. The current generic plan creates access plans for all child tables without using the paramete

RE: Speeding up creating UPDATE/DELETE generic plan for partitioned table into a lot

an be realized and this is right, so I want an opinion. Also, I'd like advice if it would be better to create a new path for partitioning like "Partition Scan Path" or "Partition Index Scan Path". regards, Sho Kato > -----Original Message- > From: Kato, Sho [ma

Speeding up creating UPDATE/DELETE generic plan for partitioned table into a lot

Sorry, I lost previous mail[1]. On Fri, 28 Dec 2018 at 20:36, Tsunakawa, Takayuki wrote: > Although I may say the same thing as you, I think a natural idea would be to > create a generic plan gradually. The starting simple question is "why do we > have to touch all partitions at first?" That

RE: Delay locking partitions during INSERT and UPDATE

gards, sho kato > -Original Message- > From: David Rowley [mailto:david.row...@2ndquadrant.com] > Sent: Friday, January 18, 2019 7:41 PM > To: Kato, Sho/加藤 翔 > Cc: PostgreSQL Hackers ; David > Rowley > Subject: Re: Delay locking partitions during INSERT and UPDATE &

RE: Speeding up creating UPDATE/DELETE generic plan for partitioned table into a lot

pdate on t (cost=0.00..382.78 rows=110 width=14) Update on t_8 -> Seq Scan on t_8 (cost=0.00..38.28 rows=11 width=14) Filter: (aid = $1) regards, > -Original Message- > From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] > Sent: Friday, Dece

Performance of SELECT in a table partitioned into a lot

Hi, I compared INSERT/UPDATE/DELETE/SELECT throughput with PostgreSQL and another dbms. For INSERT/DELETE/UPDATE, PostgreSQL performance is superior, but for SELECT, PostgreSQL performance is slightly lower than another dbms. Because information may be missing, it may be difficult, but do you k

Speeding up creating UPDATE/DELETE generic plan for partitioned table into a lot

Hi, I want to speed up the creation of UPDATE/DELETE generic plan for tables partitioned into a lot. Currently, creating a generic plan of UPDATE/DELTE for such table, planner creates a plan to scan all partitions. So it takes a very long time. I tried with a table partitioned into 8192, it took

RE: Planning time of Generic plan for a table partitioned into a lot

[mailto:langote_amit...@lab.ntt.co.jp] > Sent: Friday, November 30, 2018 3:44 PM > To: David Rowley > Cc: Kato, Sho/加藤 翔 ; PostgreSQL Hackers > > Subject: Re: Planning time of Generic plan for a table partitioned into > a lot > > On 2018/11/30 14:58, David Rowley wrote: > > On

RE: Planning time of Generic plan for a table partitioned into a lot

s of partitions. Ok, I got it. Regards, Sho Kato > -Original Message- > From: David Rowley [mailto:david.row...@2ndquadrant.com] > Sent: Thursday, November 29, 2018 7:55 PM > To: Kato, Sho/加藤 翔 > Cc: Amit Langote ; PostgreSQL Hackers > > Subject: Re: Planning time o

RE: Planning time of Generic plan for a table partitioned into a lot

rom: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] > Sent: Wednesday, November 28, 2018 2:52 PM > To: Kato, Sho/加藤 翔 ; > pgsql-hackers@lists.postgresql.org > Cc: David Rowley > Subject: Re: Planning time of Generic plan for a table partitioned into > a lot > > On 2018/11/

RE: Planning time of Generic plan for a table partitioned into a lot

all partitions with force_generic_plan, which takes time to plan. Regards, Sho Kato > -Original Message- > From: David Rowley [mailto:david.row...@2ndquadrant.com] > Sent: Wednesday, November 28, 2018 1:23 PM > To: Kato, Sho/加藤 翔 > Cc: PostgreSQL Hackers > Subject: Re:

Planning time of Generic plan for a table partitioned into a lot

Hi, I found that making a generic plan of SELECT/UPDATE/DELETE for a table partitioned into thousands is slow. Especially, UPDATE/DELETE statement is too slow. I'm afraid that I could not come up with a good idea, but how can I shorten the creation time of a generic plan? The results are as fol

RE: Performance improvements of INSERTs to a partitioned table

ge----- > From: David Rowley [mailto:david.row...@2ndquadrant.com] > Sent: Friday, November 9, 2018 11:20 AM > To: Kato, Sho/加藤 翔 ; Amit Langote > ; Robert Haas > Cc: PostgreSQL Hackers > Subject: Re: Performance improvements of INSERTs to a partitioned table > > On 7 Novem

RE: Performance improvements of INSERTs to a partitioned table

prevent this situation. But, I'm not sure this behavior is correct. Does similar deadlock occur in other DBMS like Oracle? From: Kato, Sho [mailto:kato-...@jp.fujitsu.com] Sent: Tuesday, November 6, 2018 6:36 PM To: PostgreSQL Hackers Subject: Performance improvements of INSERTs to a partit

Performance improvements of INSERTs to a partitioned table

Hi, I want to discuss performance improvements of INSERTs to a partitioned table. When an application inserts records into a table partitioned into thousands, find_all_inheritors() locks all of the partitions. Updating partition key locks in the same way. So, Execution time becomes longer as th

RE: Performance of the partitioning in the large scale

On Friday, September 28, 2018 6:03 AM, David Rowley < david.row...@2ndquadrant.com> wrote: > I think instead of attempting to highlight other bottlenecks, it might > be better to focus on lending a hand reviewing and testing the existing > set of patches. Thanks for your reply. Ok, I focus on re

Performance of the partitioning in the large scale

Hi, Thanks to your efforts the performance of the partitioned table has improved greatly. Since I evaluated the performance by combining the performance improvement patches proposed in PG12, I share it. The purpose of this evaluation is to organize performance issues of the table which is part

RE: speeding up planning with partitions

peed-up-INSERT-and-UPDATE-on-partitioned-tables.patch" (which is v8 posted > at [1]) on top of master + 0001 + 0002 + 0003, you'd get better performance > too. Thank you. I will try out these cases. Thanks, -- Sho Kato -Original Message- From: Amit Langote [mailto:lango

RE: speeding up planning with partitions

Hi, Amit Great! With the total number of records being 6400, I benchmarked while increasing the number of partitions from 100 to 6400. Applying three all patches, 20% performance improved for 100 partitions. I have the same number of records for each partition, do you do the same? Also, in my c

RE: Speeding up INSERTs and UPDATEs to partitioned tables

559.872 | 1.786138 |534.301 | 20.318 | 3.122 3200 |2161.834 | 0.462574 | 2077.737 | 72.804 | 7.037 6400 | 8282.38 | 0.120739 | 7996.212 |259.406 | 14.514 Thanks Kato Sho -Original Message- F

RE: How to make partitioning scale better for larger numbers of partitions

@jp.fujitsu.com] Sent: Monday, July 16, 2018 1:16 PM To: Kato, Sho/加藤 翔 Cc: 'Amit Langote' ; PostgreSQL mailing lists Subject: RE: How to make partitioning scale better for larger numbers of partitions From: Kato, Sho [mailto:kato-...@jp.fujitsu.com] > I did pgbench -M prepared a

RE: How to make partitioning scale better for larger numbers of partitions

--- From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] Sent: Tuesday, July 17, 2018 10:49 AM To: Ashutosh Bapat ; Kato, Sho/加藤 翔 Cc: Justin Pryzby ; PostgreSQL mailing lists Subject: Re: How to make partitioning scale better for larger numbers of partitions On 2018/07/13 22:10, Ashuto

RE: How to make partitioning scale better for larger numbers of partitions

res [.] pg_qsort 2.41% postgres postgres [.] SearchCatCache1 -Original Message- From: Tsunakawa, Takayuki/綱川 貴之 Sent: Friday, July 13, 2018 2:49 PM To: 'Amit Langote' ; Kato, Sho/加藤 翔 ; PostgreSQL mailing lists Subject: RE: How to make partitioning sca

RE: How to make partitioning scale better for larger numbers of partitions

to lock and open *all* partitions. I debug update statement execution on partitioned table. range_table_mutator seems process all leaf partitions. -Original Message- From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] Sent: Friday, July 13, 2018 1:35 PM To: Kato, Sho/加藤 翔 ; PostgreS

RE: How to make partitioning scale better for larger numbers of partitions

ecords in one table. Partitioned table has one record on each leaf partitions. Regards, -Original Message- From: Justin Pryzby [mailto:pry...@telsasoft.com] Sent: Friday, July 13, 2018 12:11 PM To: Kato, Sho/加藤 翔 Subject: Re: How to make partitioning scale better for larger numbers of part

How to make partitioning scale better for larger numbers of partitions

Hi, I benchmarked on a RANGE partitioned table with 1.1k leaf partitions and no sub-partitioned tables. But, statement latencies on a partitioned table is much slower than on a non-partitioned table. UPDATE latency is 210 times slower than a non-partitioned table. SELECT latency is 36 times slo

RE: Speeding up INSERTs and UPDATEs to partitioned tables

[mailto:david.row...@2ndquadrant.com] Sent: Thursday, July 05, 2018 6:19 PM To: Kato, Sho/加藤 翔 Cc: PostgreSQL Hackers Subject: Re: Speeding up INSERTs and UPDATEs to partitioned tables On 5 July 2018 at 18:39, Kato, Sho wrote: > postgres=# create table a(i int) partition by range(i); CREATE TA

RE: Speeding up INSERTs and UPDATEs to partitioned tables

Hi, I tried to benchmark with v1-0001-Speed-up-INSERT-and-UPDATE-on-partitioned-tables.patch, but when I create the second partition, server process get segmentation fault. I don't know the cause, but it seems that an incorrect value is set to partdesc->boundinfo. (gdb) p partdesc->boundinfo[

RE: Add function to release an allocated SQLDA

Message- From: Thomas Munro [mailto:thomas.mu...@enterprisedb.com] Sent: Monday, June 18, 2018 2:42 PM To: Kato, Sho/加藤 翔 Cc: Pg Hackers Subject: Re: Add function to release an allocated SQLDA On Wed, Jun 13, 2018 at 4:29 PM, Kato, Sho wrote: > I add a function called ECPGfreeSQLDA() becasue th

Add function to release an allocated SQLDA

JPEXMBYT05#0A3221C70F24FB45833433255569204D1F8AD5D6@G01JPEXMBYT05 regards, -- Kato Sho Add-ECPGfreeSQLDA.patch Description: Add-ECPGfreeSQLDA.patch

RE: [doc fix] Add operation of freeing output SQLDA

tor as a key and free the descriptor. So, simply DEALLOCATE DESCRIPTOR cannot be used for sqlda. If free is undesirable, how would you think to make an ecpg API for free? regards, -Original Message- From: Kyotaro HORIGUCHI [mailto:horiguchi.kyot...@lab.ntt.co.jp] Sent: Tuesday, May 22, 2018 10

[doc fix] Add operation of freeing output SQLDA

x27;s memory space remain. ecpg regression test also free output SQLDA's memory space. The attached patch fixes the documentation. regards, -- Kato Sho sqlda_doc.patch Description: sqlda_doc.patch

Scenario using pg_rewind

source server, does it? regards, -- Kato Sho