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

help / color / mirror / Atom feed
From: Jesper Pedersen <jesper.pedersen@redhat.com>
To: Imai, Yoshikazu <imai.yoshikazu@jp.fujitsu.com>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Amit Langote <amitlangote09@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Rowley <david.rowley@2ndquadrant.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: speeding up planning with partitions
Date: Wed, 20 Mar 2019 10:25:05 -0400
Message-ID: <53a80692-c68d-5e11-1014-6441b6e8ca5a@redhat.com> (raw)
In-Reply-To: <0F97FA9ABBDBE54F91744A9B37151A512B45CD@g01jpexmbkw24>
References: <CAKJS1f9XAePd2+8F4=wtHKdDdckgKcSoXb-ZbU8Wbt86mhLxKA@mail.gmail.com>
	<b5362c29-cca1-de7b-13d0-9e51f9911185@lab.ntt.co.jp>
	<80afef3f-1870-5cb3-eeaf-7d8ecb41994c@lab.ntt.co.jp>
	<af3da198-e4aa-0535-8b13-40b02a4a7c41@lab.ntt.co.jp>
	<d768d90d-628e-6cdc-513d-f50a0ce02cea@redhat.com>
	<CA+HiwqFTqUVuiyK7dDQDCrNF1NiPDOAnmQum2jcw1yj5Z+kC0Q@mail.gmail.com>
	<0F97FA9ABBDBE54F91744A9B37151A5129DD55@g01jpexmbkw24>
	<256b9c87-b687-6f51-6274-19ae5743aef6@lab.ntt.co.jp>
	<0F97FA9ABBDBE54F91744A9B37151A5129E604@g01jpexmbkw24>
	<9feacaf6-ddb3-96dd-5b98-df5e927b1439@lab.ntt.co.jp>
	<0F97FA9ABBDBE54F91744A9B37151A512B3279@g01jpexmbkw24>
	<0c90ce90-b010-4791-41c0-a0df34f99ffa@lab.ntt.co.jp>
	<0F97FA9ABBDBE54F91744A9B37151A512B3528@g01jpexmbkw24>
	<d964510e-2eec-ce42-8ad8-884caf718e03@lab.ntt.co.jp>
	<0F97FA9ABBDBE54F91744A9B37151A512B359C@g01jpexmbkw24>
	<72094fc9-3232-0fc7-b7cd-42e1c73d5aee@lab.ntt.co.jp>
	<0F97FA9ABBDBE54F91744A9B37151A512B45CD@g01jpexmbkw24>

Hi,

On 3/19/19 11:15 PM, Imai, Yoshikazu wrote:
> Here the details.
> 
> [creating partitioned tables (with 1024 partitions)]
> drop table if exists rt;
> create table rt (a int, b int, c int) partition by range (a);
> \o /dev/null
> select 'create table rt' || x::text || ' partition of rt for values from (' ||
>   (x)::text || ') to (' || (x+1)::text || ');' from generate_series(1, 1024) x;
> \gexec
> \o
> 
> [select1024.sql]
> \set a random (1, 1024)
> select * from rt where a = :a;
> 
> [pgbench]
> pgbench -n -f select1024.sql -T 60
> 
> 

My tests - using hash partitions - shows that the extra time is spent in 
make_partition_pruneinfo() for the relid_subplan_map variable.

   64 partitions: make_partition_pruneinfo() 2.52%
8192 partitions: make_partition_pruneinfo() 5.43%

TPS goes down ~8% between the two. The test setup is similar to the above.

Given that Tom is planning to change the List implementation [1] in 13 I 
think using the palloc0 structure is ok for 12 before trying other 
implementation options.

perf sent off-list.

[1] https://www.postgresql.org/message-id/24783.1551568303%40sss.pgh.pa.us

Best regards,
  Jesper




view thread (265+ messages)  latest in thread

Message-ID: <53a80692-c68d-5e11-1014-6441b6e8ca5a@redhat.com>
Permalink:  ../53a80692-c68d-5e11-1014-6441b6e8ca5a@redhat.com/
Also on:    postgresql.org/message-id/53a80692-c68d-5e11-1014-6441b6e8ca5a@redhat.com

 · 

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: jesper.pedersen@redhat.com, imai.yoshikazu@jp.fujitsu.com, Langote_Amit_f8@lab.ntt.co.jp, amitlangote09@gmail.com, tgl@sss.pgh.pa.us, david.rowley@2ndquadrant.com, alvherre@2ndquadrant.com, robertmhaas@gmail.com, pryzby@telsasoft.com
  Subject: Re: speeding up planning with partitions
  In-Reply-To: <53a80692-c68d-5e11-1014-6441b6e8ca5a@redhat.com>

* 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