From: Ravi Garg <ravi.garg@yahoo.com>
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: PostgreSQL 11 higher Planning time on Partitioned table
Date: Sun, 23 Feb 2020 09:56:30 +0000 (UTC)
Message-ID: <1789489940.5008704.1582451790367@mail.yahoo.com> (raw)
References: <1789489940.5008704.1582451790367.ref@mail.yahoo.com>
Hi,
I am looking to Range Partition one of my table (i.e. TransactionLog) in PostgreSQL 11.While evaluating query performance difference between the un-partitioned and partitioned table I am getting huge difference in planning time. Planning time is very high on partitioned table.Similarly when 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 table (parent table) name in query, where planning time **6.231 ms** (Samples below).<br>
Below are the details, Let me know how can I improve query performance on partitioned table.
Following is the schema CREATE TABLE TransactionLog (
txid character varying(36) NOT NULL, txnDetails character varying(64), loggingtime timestamp(6) without time zone DEFAULT LOCALTIMESTAMP,) PARTITION BY RANGE(loggingtime);
CREATE TABLE IF NOT EXISTS TransactionLog_20200223 PARTITION OF TransactionLog FOR VALUES FROM ('2020-02-23') TO ('2020-02-24');CREATE UNIQUE INDEX TransactionLog_20200223_UnqTxId ON TransactionLog_20200223 (txnid);
Following is explain analyze result when I query Directly on partition. Planning time ~**0.080 ms** (average of 10 execution)postgres=> EXPLAIN (ANALYZE,VERBOSE,COSTS,BUFFERS,TIMING,SUMMARY) select txnDetails FROM mra_part.TransactionLog_20200223 WHERE txnid = 'febd139d-1b7f-4564-a004-1b3474e51756'; QUERY PLAN--------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using TransactionLog_20200223_UnqTxId on TransactionLog_20200223 (cost=0.57..4.61 rows=1 width=10) (actual time=0.039..0.040 rows=1 loops=1) Output: txnDetails Index Cond: ((TransactionLog_20200223.txnid)::text = 'febd139d-1b7f-4564-a004-1b3474e51756'::text) Buffers: shared hit=5 **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 execution)postgres=> EXPLAIN (ANALYZE,VERBOSE,COSTS,BUFFERS,TIMING,SUMMARY) select txnDetails FROM mtdauthlog WHERE txnid = 'febd139d-1b7f-4564-a004-1b3474e51756' AND loggingtime >= '2020-02-23'::timestamp without time zone AND loggingtime < '2020-02-24'::timestamp without time zone; QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.57..4.62 rows=1 width=10) (actual time=0.036..0.037 rows=1 loops=1) Buffers: shared hit=5 -> Index Scan using TransactionLog_20200223_UnqTxId on TransactionLog_20200223 (cost=0.57..4.61 rows=1 width=10) (actual time=0.035..0.036 rows=1 loops=1) Output: TransactionLog_20200223.txnDetails Index Cond: ((TransactionLog_20200223.txnid)::text = 'febd139d-1b7f-4564-a004-1b3474e51756'::text) Filter: ((TransactionLog_20200223.loggingtime >= '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=5 **Planning Time: 6.231 ms** Execution Time: 0.076 ms(9 rows)
There are around ~200 child partitions. Partition pruning enabled.PostgreSQL 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,
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-performance@postgresql.org
Cc: ravi.garg@yahoo.com, pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL 11 higher Planning time on Partitioned table
In-Reply-To: <1789489940.5008704.1582451790367@mail.yahoo.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