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 1j5o09-00006U-GI for pgsql-performance@arkaria.postgresql.org; Sun, 23 Feb 2020 09:56:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j5o06-0005zk-WC for pgsql-performance@arkaria.postgresql.org; Sun, 23 Feb 2020 09:56:38 +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 1j5o06-0005zd-BF for pgsql-performance@lists.postgresql.org; Sun, 23 Feb 2020 09:56:38 +0000 Received: from sonic312-20.consmr.mail.bf2.yahoo.com ([74.6.128.82]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j5o03-00047b-6d for pgsql-performance@lists.postgresql.org; Sun, 23 Feb 2020 09:56:37 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1582451792; bh=4JsHSmbI2O8EXYF5Kz67WDa3k0+LPZlGgetoRhMKhfg=; h=Date:From:Reply-To:To:Subject:References:From:Subject; b=uJTIikzE9nn4GK8hLYHhx29wTEnOvjcisYbLFi7NP346IJNwPysWDrL4RjkVn4wbqIkKyqMvki5HhqArdNwKuIPL0PqUhmy/OW6QubBMVI6Qs70lsBzboH5SsMT06I8ytwcpnIDHh9yuq145R6Y6ZX9HHJUuGFGeskw3vkHirT8CuCuI1+EcxEnVMZEdhJVhOS+nldMWMZ5eRLTVPjMLFIIcuPSwkPiEA+57/2vRmbP2+LlOYR2l7NDOOZegOHP2b5OpqAyfHE72g0n8MPIoa5Hmxrh8pONAa1etaTLxiLnVFAoqdpwoqlJH5vuQGMbXzo/kpd8z5xqfp5xnapHvyg== X-YMail-OSG: pkJgcTQVM1k30Yz_1UO.il1e9_pr7uvJHA14c4LlrTI__ocuF1NPylgbvbdRqtT sgjDTO8vy7VPrfXcPGQml6OgApJ4GQ9W_OBZyml6acgsVkMEUuikb462kYamDAwB_SlFr8z3WLnC q6tbukcdriJmrRp6IzqV3k7aAuay.1QFuAfjwxViXHBhabWxpsVjBRlfI4YjYrfIi9UnkopWUtFK NjcegzRicoU7I2EjJwhkxkluBBsAU0udp92Fgo.QmwHMArW9nDxSx44sZYR1Vn.j6TE5M7vsUrFj DipPRwfc.9MHS8YJsquhD4hMzmxby3DYdbu82lxMTEFydtyDKIN_FU9eFN2c2T_8jBibVBUU_hnk Rgu1KwQG1XnWzP.xF1QyzGvuYFcniBn.ogqkwXLnCOecOqFzOYwYqC1PjGrrcUX92_UAZ7DW759A qF4waA5A_8.hts8ywZiEisRiOBriSD53bcLQtu7LWiCgRWH4DE.TYxufq5d1aHJBfJgqglIyNVmL xJ4fMlNuiKYSYzu1_GhUEegwHsxUkq6z6UJE_pf7n7hXhmyUIcLRNf3KmGiLgA6BXY4mLDymvO.K oKbBsmjSb3Zs1b_t3uMrA7u_CZQgG36jzUiGg5dYNr9o5.CQG7akmctXMSh6ke95H3YG8oUZOWVx 8vaxiphaj8aC086EQq5HioUQbKxWTG8Wz6NS6ugzaRHmjVXyg5Oooeuallw9Wmk1W6u9a3Vx7EcJ ABqLliMxFBPlVhon.FrWWCJEUb5F3A2dpCIab7arImVfCMwvcOG0BLyMLRpZnvXjms99YWQ9Dg5V _pEWlI06JFSVshl4kEcGtltJKNvh9m.x8Dp3e89LFcALCdetIJAh4OfW4jiIo.CIY_R9a8QmyR.9 nc3V2iWxsswWTt.fv4D9AGIiEUOHP2TIlX.lO85kxGUbHJgYLP7BlaR0l3x0B8.2HS1LG9pPZu36 taL6SqiGzj.amR9gurrU.GdkLZXcpcRhtsgKEBS9zwHpbU5p2ksArvBlVH8ypzCcs5QupnNe98a0 yAngWFnqDHBVVKeTaVAJlIi7RpcBMahZgsT5QSv9rdgRl_J0glYjd0Mla79yL7KjCwl1phw3_6uD OipyQ7Bgjwr5JogOCR7jmOoELLM2xQ8RMiUVL6YRs7hT82h4KXYpC6Fbaybh8IoFx8LQs3ny5dmP coNQjXOAeke0zqT4og5tZwMX.OOey1Tp61TmkqUc913uTEuxwMQo6ngic10xqPB87s5Y1U4u0l_H EUBfv7PllxPBWIbd3sQofXKuvZqZLM6agXmDYyK1.aaNvTgXbUhFkXeS809wT0OBPFRoKc.95uy. ULY.DaNd3MIpJ3_fOu8yRLCeA6DvH3uctAWnr_qs- Received: from sonic.gate.mail.ne1.yahoo.com by sonic312.consmr.mail.bf2.yahoo.com with HTTP; Sun, 23 Feb 2020 09:56:32 +0000 Date: Sun, 23 Feb 2020 09:56:30 +0000 (UTC) From: Ravi Garg Reply-To: Ravi Garg To: "pgsql-performance@lists.postgresql.org" Message-ID: <1789489940.5008704.1582451790367@mail.yahoo.com> Subject: PostgreSQL 11 higher Planning time on Partitioned table MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5008703_251248542.1582451790365" References: <1789489940.5008704.1582451790367.ref@mail.yahoo.com> X-Mailer: WebService/1.1.15199 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.97 Safari/537.36 Content-Length: 14984 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_5008703_251248542.1582451790365 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi, I am looking to Range Partition one of my table (i.e. TransactionLog) in Po= stgreSQL 11.While evaluating query performance difference between the un-pa= rtitioned and partitioned table I am getting huge difference in planning ti= me. Planning time is very high on partitioned table.Similarly when I query = by specifying partition name directly in query the planning time is much le= ss **0.081 ms** as compared to when I query based on partition table (paren= t table) name in query, where planning time **6.231 ms** (Samples below). Below are the details, Let me know how can I improve query performance on p= artitioned table. Following is the schema=C2=A0CREATE TABLE TransactionLog ( =C2=A0 =C2=A0 txid character varying(36) NOT NULL,=C2=A0 =C2=A0 txnDetails = character varying(64),=C2=A0 =C2=A0 loggingtime timestamp(6) without time z= one DEFAULT LOCALTIMESTAMP,) PARTITION BY RANGE(loggingtime); CREATE TABLE IF NOT EXISTS TransactionLog_20200223 PARTITION OF Transaction= Log FOR VALUES FROM ('2020-02-23') TO ('2020-02-24');CREATE UNIQUE INDEX Tr= ansactionLog_20200223_UnqTxId ON TransactionLog_20200223 (txnid); Following is explain analyze result when I query Directly on partition. Pla= nning time ~**0.080 ms** (average of 10 execution)postgres=3D> EXPLAIN (ANA= LYZE,VERBOSE,COSTS,BUFFERS,TIMING,SUMMARY) select txnDetails FROM mra_part.= TransactionLog_20200223 WHERE txnid =3D 'febd139d-1b7f-4564-a004-1b3474e517= 56';=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN----------= ---------------------------------------------------------------------------= --------------------------------------------------------=C2=A0Index Scan us= ing TransactionLog_20200223_UnqTxId on TransactionLog_20200223 (cost=3D0.57= ..4.61 rows=3D1 width=3D10) (actual time=3D0.039..0.040 rows=3D1 loops=3D1)= =C2=A0 =C2=A0Output: txnDetails=C2=A0 =C2=A0Index Cond: ((TransactionLog_20= 200223.txnid)::text =3D 'febd139d-1b7f-4564-a004-1b3474e51756'::text)=C2=A0= =C2=A0Buffers: shared hit=3D5=C2=A0**Planning Time: 0.081 ms**=C2=A0Execut= ion Time: 0.056 ms(6 rows) Following is explain analyze result when I query by parent-table. Planning = time **6.198 ms** (average of 10 execution)postgres=3D> EXPLAIN (ANALYZE,VE= RBOSE,COSTS,BUFFERS,TIMING,SUMMARY)=C2=A0 select txnDetails FROM mtdauthlog= WHERE txnid =3D 'febd139d-1b7f-4564-a004-1b3474e51756' AND loggingtime >= =3D '2020-02-23'::timestamp without time zone AND loggingtime < '2020-02-24= '::timestamp without time zone;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PL= AN-------------------------------------------------------------------------= ---------------------------------------------------------------=C2=A0Append= =C2=A0 (cost=3D0.57..4.62 rows=3D1 width=3D10) (actual time=3D0.036..0.037 = rows=3D1 loops=3D1)=C2=A0 =C2=A0Buffers: shared hit=3D5=C2=A0 =C2=A0->=C2= =A0 Index Scan using TransactionLog_20200223_UnqTxId on TransactionLog_2020= 0223=C2=A0 (cost=3D0.57..4.61 rows=3D1 width=3D10) (actual time=3D0.035..0.= 036 rows=3D1 loops=3D1)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Output: Transactio= nLog_20200223.txnDetails=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: ((Tra= nsactionLog_20200223.txnid)::text =3D 'febd139d-1b7f-4564-a004-1b3474e51756= '::text)=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: ((TransactionLog_20200223= .loggingtime >=3D '2020-02-23 00:00:00'::timestamp without time zone) AND (= TransactionLog_20200223.loggingtime < '2020-02-24 00:00:00'::timestamp with= out time zone))=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Buffers: shared hit=3D5=C2= =A0**Planning Time: 6.231 ms**=C2=A0Execution Time: 0.076 ms(9 rows) There are around ~200 child partitions. Partition pruning enabled.PostgreSQ= L Version: PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.= 8.5 20150623 (Red Hat 4.8.5-39), 64-bit Thanks and Regards, Ravi Garg, ------=_Part_5008703_251248542.1582451790365 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi,

I am looking to Range Partition on= e of my table (i.e. TransactionLog) in PostgreSQL 11.
= While evaluating query performance difference between the un-partitioned an= d partitioned table I am getting huge difference in planning time. Planning= time is very high on partitioned table.
Similarly whe= n I query by specifying partition name directly in query the planning time = is much less **0.081 ms** as compared to when I query based on partition ta= ble (parent table) name in query, where planning time **6.231 ms** (Samples= below).<br>

Below are the details, Let me know how can I impro= ve query performance on partitioned table.

<= div style=3D"font-family: Helvetica Neue, Helvetica, Arial, sans-serif; fon= t-size: 13px;">Following is the schema 
CR= EATE TABLE TransactionLog (
    txid cha= racter varying(36) NOT NULL,
    txnDetails = character varying(64),
    loggingtime times= tamp(6) without time zone DEFAULT LOCALTIMESTAMP,
) PA= RTITION BY RANGE(loggingtime);

CREATE TABLE IF NOT EXISTS TransactionLog_20200223 PARTITION OF Transact= ionLog FOR VALUES FROM ('2020-02-23') TO ('2020-02-24');
CREATE UNIQUE INDEX TransactionLog_20200223_UnqTxId ON TransactionLog_20= 200223 (txnid);


Following is explain analyze result when I query Directly = on partition. Planning time ~**0.080 ms** (average of 10 execution)
postgres=3D> EXPLAIN (ANALYZE,VERBOSE,CO= STS,BUFFERS,TIMING,SUMMARY) select txnDetails FROM mra_part.TransactionLog_= 20200223 WHERE txnid =3D 'febd139d-1b7f-4564-a004-1b3474e51756';
               = ;                     &nb= sp;                     &= nbsp;                  QUERY P= LAN
----------------------------------------= ---------------------------------------------------------------------------= --------------------------
 Index Scan = using TransactionLog_20200223_UnqTxId on TransactionLog_20200223 (cost=3D0.= 57..4.61 rows=3D1 width=3D10) (actual time=3D0.039..0.040 rows=3D1 loops=3D= 1)
   Output: txnDetails
   Index Cond: ((TransactionLog_20200223.tx= nid)::text =3D 'febd139d-1b7f-4564-a004-1b3474e51756'::text)
   Buffers: shared hit=3D5
 **Planning Time: 0.081 ms**=
 Execution Time: 0.056 ms
(6 rows)


Following is explain analyze result= when I query by parent-table. Planning time **6.198 ms** (average of 10 ex= ecution)
postgres=3D> EXPLAIN (AN= ALYZE,VERBOSE,COSTS,BUFFERS,TIMING,SUMMARY)  select txnDetails FROM mt= dauthlog WHERE txnid =3D 'febd139d-1b7f-4564-a004-1b3474e51756' AND logging= time >=3D '2020-02-23'::timestamp without time zone AND loggingtime <= '2020-02-24'::timestamp without time zone;
=                      = ;                     &nb= sp;                     &= nbsp;                    =         QUERY PLAN
----= ---------------------------------------------------------------------------= ---------------------------------------------------------
 Append  (cost=3D0.57..4.62 rows=3D1 width=3D10) (a= ctual time=3D0.036..0.037 rows=3D1 loops=3D1)
<= font size=3D"1" face=3D"courier new, courier, monaco, monospace, sans-serif= ">   Buffers: shared hit=3D5
 = ;  ->  Index Scan using TransactionLog_20200223_UnqTxId on Tra= nsactionLog_20200223  (cost=3D0.57..4.61 rows=3D1 width=3D10) (actual = time=3D0.035..0.036 rows=3D1 loops=3D1)
&nbs= p;        Output: TransactionLog_20200223.txnDetails
         Index Cond: = ((TransactionLog_20200223.txnid)::text =3D 'febd139d-1b7f-4564-a004-1b3474e= 51756'::text)
        &n= bsp;Filter: ((TransactionLog_20200223.loggingtime >=3D '2020-02-23 00:00= :00'::timestamp without time zone) AND (TransactionLog_20200223.loggingtime= < '2020-02-24 00:00:00'::timestamp without time zone))
         Buffers: shared hit=3D5
 **Planning Time: 6.= 231 ms**
 Execution Time: 0.076 ms<= /font>
(9 rows)

There = are around ~200 child partitions. Partition pruning enabled.
PostgreSQL Version: PostgreSQL 11.7 on x86_64-pc-linux-gn= u, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit

Thanks and Regards,
Ravi Garg,

------=_Part_5008703_251248542.1582451790365--