pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: David Rowley <david.rowley@2ndquadrant.com>
To: Imai, Yoshikazu <imai.yoshikazu@jp.fujitsu.com>
Cc: Tsunakawa, Takayuki <tsunakawa.takay@jp.fujitsu.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: speeding up planning with partitions
Date: Wed, 23 Jan 2019 10:21:00 +0900
Message-ID: <2c41bcc5-cbe7-d0a6-4fe3-bebe80dbf21e@lab.ntt.co.jp> (raw)
In-Reply-To: <CAKJS1f-dn1hDZqObwdMrYdV7-cELJwWCPRWet6EQX_WaV8JLgw@mail.gmail.com>
References: <20181107033207.cdisp33ryrgeidef@alvherre.pgsql>
	<f74d2f85-a9c3-f82d-188a-fcfe77911e78@lab.ntt.co.jp>
	<3be67028-a00a-502c-199a-da00eec8fb6e@lab.ntt.co.jp>
	<CAKJS1f8g9_BzE678BLBm-eoMMEYUUXhDABSpqtAHRUUTrm_vFA@mail.gmail.com>
	<a22f24d1-5e20-f352-f402-3101cb1213bd@lab.ntt.co.jp>
	<73596f90-023c-c358-be9a-3c21bffcb15d@lab.ntt.co.jp>
	<0F97FA9ABBDBE54F91744A9B37151A5122E7B1@g01jpexmbkw24>
	<08a6775f-7c88-81a8-8c2c-984d171b6db9@lab.ntt.co.jp>
	<0F97FA9ABBDBE54F91744A9B37151A51256276@g01jpexmbkw24>
	<1cb56f0f-43f6-5d36-35c7-9d5e7199eeed@lab.ntt.co.jp>
	<0F97FA9ABBDBE54F91744A9B37151A51259637@g01jpexmbkw24>
	<CAKJS1f_3aiqfxLT9q4Sdjiog1kWxEMpmVU=Yu-RKyYYoA=p9XQ@mail.gmail.com>
	<0F97FA9ABBDBE54F91744A9B37151A51259C4B@g01jpexmbkw24>
	<0A3221C70F24FB45833433255569204D1FB6CFCA@G01JPEXMBYT05>
	<0F97FA9ABBDBE54F91744A9B37151A51259E1A@g01jpexmbkw24>
	<CAKJS1f-dn1hDZqObwdMrYdV7-cELJwWCPRWet6EQX_WaV8JLgw@mail.gmail.com>

On 2019/01/22 18:47, David Rowley wrote:
> On Tue, 22 Jan 2019 at 20:01, Imai, Yoshikazu
>> What I understand so far is about 10,000 while loops at total (4098+4098+some extra) is needed in hash_seq_search() in EXECUTE query after the creation of the generic plan.
>> 10,000 while loops takes about 10 microsec (of course, we can't estimate correct time), and the difference of the latency between 5th and 7th EXECUTE is about 8 microsec, I currently think this causes the difference.
>  >
>> I don't know this problem relates to Amit-san's patch, but I'll continue to investigate it.
> 
> I had another thought... when you're making a custom plan you're only
> grabbing locks on partitions that were not pruned (just 1 partition in
> your case), but when making the generic plan, locks will be acquired
> on all partitions (all 4000 of them). This likely means that when
> building the generic plan for the first time that the
> LockMethodLocalHash table is expanded to fit all those locks, and
> since we never shrink those down again, it'll remain that size for the
> rest of your run.  I imagine the reason for the slowdown is that
> during LockReleaseAll(), a sequential scan is performed over the
> entire hash table. I see from looking at the hash_seq_search() code
> that the value of max_bucket is pretty critical to how it'll perform.
> The while ((curElem = segp[segment_ndx]) == NULL) loop will need to
> run fewer times with a lower max_bucket.

I too think that that might be behind that slight drop in performance.
So, it's good to know what one of the performance bottlenecks is when
dealing with large number of relations in queries.

Thanks,
Amit





view thread (265+ messages)  latest in thread

Message-ID: <2c41bcc5-cbe7-d0a6-4fe3-bebe80dbf21e@lab.ntt.co.jp>
Permalink:  ../2c41bcc5-cbe7-d0a6-4fe3-bebe80dbf21e@lab.ntt.co.jp/
Also on:    postgresql.org/message-id/2c41bcc5-cbe7-d0a6-4fe3-bebe80dbf21e@lab.ntt.co.jp

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: Langote_Amit_f8@lab.ntt.co.jp, david.rowley@2ndquadrant.com, imai.yoshikazu@jp.fujitsu.com, tsunakawa.takay@jp.fujitsu.com, alvherre@2ndquadrant.com
  Subject: Re: speeding up planning with partitions
  In-Reply-To: <2c41bcc5-cbe7-d0a6-4fe3-bebe80dbf21e@lab.ntt.co.jp>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox