Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hxQJ1-0004KU-4P for pgsql-performance@arkaria.postgresql.org; Tue, 13 Aug 2019 06:29:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hxQIz-0006pS-ST for pgsql-performance@arkaria.postgresql.org; Tue, 13 Aug 2019 06:29:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hxQIz-0006nS-Eh for pgsql-performance@lists.postgresql.org; Tue, 13 Aug 2019 06:29:13 +0000 Received: from mail-lf1-x144.google.com ([2a00:1450:4864:20::144]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hxQIr-0002Re-PB for pgsql-performance@lists.postgresql.org; Tue, 13 Aug 2019 06:29:12 +0000 Received: by mail-lf1-x144.google.com with SMTP id 62so71074196lfa.8 for ; Mon, 12 Aug 2019 23:29:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=message-id:mime-version:to:cc:from:subject:date:importance :in-reply-to:references; bh=ZlFeSHduDQVK8PWLaZt1M+vCegNt01sCsVTVAalKSFU=; b=mczfNZOKYzqpUfTpJF14hcUVCtvUzi/R755KHdO80zNLxtkP1UVkKhKDMeUoIswup3 jZ6cxeIvtc7jrfzJaHRmlJ82dbjYksgggYJzl/2ZsD6rDAQNiv7AnGmcdhsbDxYG3O6U r1QrO9PvRY4yN2/I9+7g9DZ+7f6LmjjB7rlx4EJkzK8AYpylXSSGXCdvOg2A7vgTZnQ6 SoMt5EDpwiGpfUxq5Lecrtx4BYIAo72UnhYk0KoG8pcMx4Oh24S5Qad8WACazo9f5d2e +wL8WZfoJOhI15+B6uzuCW3uOvqKhVB/+bT+6vhZ/Y9kOH/6NmiNrk1U/vQEWG9sX4k3 FrHg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:message-id:mime-version:to:cc:from:subject:date :importance:in-reply-to:references; bh=ZlFeSHduDQVK8PWLaZt1M+vCegNt01sCsVTVAalKSFU=; b=QpRuIpULZIif/x684eCu3/97y4I3o3y6JShD36av7h9in7N+a55TLAlFGPGCNq2dl3 rb46PvKLqBIddqWhKnXRF5NtUJ7EOsL7CnMjb/XNgoMmOjbEgpnt2PKumfCUxt4Pivnh FWZ8W3bcnHk3obHd27LHrmf3wjr2f/8AbB08cFImOD07/arskGk69Ll/Vqm/VwbRqZWC eFUSA4CVQHRvAAFm3PXSUOs8TR0cfMDQQ67sFFUpOYk1g2cOxaN8e9rddXMPrV0TKoEh eXgCgX48SnJg64nmUVWEuzkpuV8ASpA9S0l0/MyldurGib3d8FeZ+c20Fq8MGuhmJlhk vjIA== X-Gm-Message-State: APjAAAVW4xv2/zTyAtzOun6ynxf5EVVdefzFIwLFgPJltdTJKw3Kcxum vVg5FfQc/a8zj2KOR1VRS2NR4fonYTQJjQ== X-Google-Smtp-Source: APXvYqzEbyUwRE6cQFxVEoSipa951ZEElp6Z9otDd56zXFW9ovg0J/ycEGwV3j1x2NdFeMmx5rGJ4A== X-Received: by 2002:a19:9104:: with SMTP id t4mr20813642lfd.179.1565677744682; Mon, 12 Aug 2019 23:29:04 -0700 (PDT) Received: from ?IPv6:::ffff:192.168.1.2? ([212.180.219.12]) by smtp.gmail.com with ESMTPSA id f22sm2327018ljh.22.2019.08.12.23.29.03 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 12 Aug 2019 23:29:04 -0700 (PDT) Message-ID: <5d5258b0.1c69fb81.3d155.d4f8@mx.google.com> MIME-Version: 1.0 To: Michael Lewis , David Rowley Cc: MichaelDBA , =?utf-8?Q?Piotr_W=C5=82odarczyk?= , "pgsql-performance@lists.postgresql.org" From: =?utf-8?Q?Piotr_W=C5=82odarczyk?= Subject: ODP: Planner performance in partitions Date: Tue, 13 Aug 2019 08:29:06 +0200 Importance: normal X-Priority: 3 In-Reply-To: References: <5675f9f771f04d9f8b5cffe16f4a4175@EX13MBX02.gb.ad.corp> <458dfc43-b097-29f3-4e0e-857b9c30b427@sqlexec.com> <5d51c622.1c69fb81.4032d.34d9@mx.google.com> Content-Type: multipart/alternative; boundary="_AF596A09-A3E5-4A96-ADA0-94270D47182B_" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_AF596A09-A3E5-4A96-ADA0-94270D47182B_ Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset="utf-8" As you wrote we have about 400/500 partitions in real life. So time problem= is much smaller, but still it is and in one place of aur application we ha= ve decided to help DB and we're indicating in query exact partition we need= . What pushed me to do this test? Just curiosity I think. After I saw in pg= _locks that all partitions which was selected in uncommitted transaction ha= ve ACCESS SHARED i've started thinking about efficiency. And that way here = we are. Why we need some hundred partitions? It=E2=80=99s because our main = table (public.book) have hundreds of millions records. It=E2=80=99s not mai= ntainable. VACUUM never ends, space on device is huge and we cannot take da= tabase down for longer that 2-3 hours, what is too short to maintain them m= anually. So we've partitioned them on two levels. First on id_owner (which = is in every query) and the second level based on date. It=E2=80=99ll help a= s detach partitions with old data we no longer need.=20 =20 Pozdrawiam, Piotr W=C5=82odarczyk Od: Michael Lewis Wys=C5=82ano: wtorek, 13 sierpnia 2019 00:37 Do: David Rowley DW: Piotr W=C5=82odarczyk; MichaelDBA; Piotr W=C5=82odarczyk; pgsql-perform= ance@lists.postgresql.org Temat: Re: Planner performance in partitions Was there a reason to exceed 100-500 partitions in real life that pushed yo= u to do this test? Is there some issue you see when using 100 partitions th= at is solved or reduced in severity by increasing to 1200 or 6000 partition= s? --_AF596A09-A3E5-4A96-ADA0-94270D47182B_ Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset="utf-8"

As you wrote we have about 400/500 par= titions in real life. So time problem is much smaller, but still it is and = in one place of aur application we have decided to help DB and we're indica= ting in query exact partition we need. What pushed me to do this test? Just= curiosity I think. After I saw in pg_locks that all partitions which was s= elected in uncommitted transaction have ACCESS SHARED i've started thinking= about efficiency. And that way here we are. Why we need some hundred parti= tions? It=E2=80=99s because our main table (public.book) have hundreds of m= illions records. It=E2=80=99s not maintainable. VACUUM never ends, space on= device is huge and we cannot take database down for longer that 2-3 hours,= what is too short to maintain them manually. So we've partitioned them on = two levels. First on id_owner (which is in every query) and the second leve= l based on date. It=E2=80=99ll help as detach partitions with old data we n= o longer need.

 

Pozdrawiam,

Piotr W=C5=82odarczyk

 

Od: Michael Lewis
Wys=C5=82ano: wtorek, 13 sierpnia 201= 9 00:37
Do: David= Rowley
DW: Pi= otr W=C5=82odarczyk; MichaelD= BA; Piotr W=C5=82odarczyk; pgsql-performa= nce@lists.postgresql.org
Temat: Re: Planner performance in pa= rtitions

 

Was there a reason to exceed 100-500 partitions in real life that pus= hed you to do this test? Is there some issue you see when using 100 partiti= ons that is solved or reduced in severity by increasing to 1200 or 6000 par= titions?

 

= --_AF596A09-A3E5-4A96-ADA0-94270D47182B_--