pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPartition table Creation and Testing using Inherited method
5+ messages / 2 participants
[nested] [flat]
* Partition table Creation and Testing using Inherited method
@ 2021-03-03 03:19 dbatoCloud Solution <dbatocloud17@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: dbatoCloud Solution @ 2021-03-03 03:19 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Dear All!
Good Day!
Someone guide me on partitioning, I used the declarative method(
inheritance), Created child tables, added check constraints and steps below
which I mentioned it.
*step1 #*
CREATE TABLE core.contact_transaction_history (
record_insert_datetime timestamp NULL,
crm_contact_type_id int4 NULL
);
*step2#*
CREATE TABLE core.contact_transaction_history_Year_2016() INHERITS
(core.contact_transaction_history_PartMar2021);
CREATE TABLE core.contact_transaction_history_Year_2017() INHERITS
(core.contact_transaction_history_PartMar2021);
CREATE TABLE core.contact_transaction_history_Year_2018() INHERITS
(core.contact_transaction_history_PartMar2021);
!
!
!
CREATE TABLE core.contact_transaction_history_Year_2025() INHERITS
(core.contact_transaction_history_PartMar2021);
*step3#*
ALTER TABLE core.contact_transaction_history_Year_2016 ADD CHECK
(record_insert_datetime >= '2016-01-01' AND record_insert_datetime <
'2016-12-31');
!
!
!
ALTER TABLE core.contact_transaction_history_Year_2025 ADD CHECK
(record_insert_datetime >= '2025-01-01' AND record_insert_datetime <
'2025-12-31');
*step4#*
ALTER TABLE core.contact_transaction_history ADD CONSTRAINT
contact_transaction_history_pkey PRIMARY KEY (contact_transaction_id)
ALTER TABLE core.contact_transaction_history ADD CONSTRAINT
contact_transaction_history_staging_contact_id_fkey_32201 FOREIGN KEY
(contact_id) REFERENCES core.contact(contact_id);
---
*Analyze Query performance # *
*After creating the partition* table then I inserted records(count:
313494). After that, I ran explain anaylze
explain analyze select * from core.contact_transaction_history where
record_insert_datetime >= '2016-01-01' and record_insert_datetime
<='2020-12-31'
Append (cost=0.00..14698.13 rows=313498 width=1707) (actual
time=0.010..98.887 rows=313494 loops=1)
-> Seq Scan on contact_transaction_history (cost=0.00..14686.41
rows=313493 width=1707) (actual time=0.010..77.302 rows=313494 loops=1)
Filter: ((record_insert_datetime >= '2016-01-01
00:00:00'::timestamp without time zone) AND (record_insert_datetime <=
'2020-12-31 00:00:00'::timestamp without time zone))
-> Index Scan using record_insert_datetime_2016_idx on
contact_transaction_history_year_2016 (cost=0.12..2.34 rows=1 width=13934)
(actual time=0.017..0.018 rows=0 loops=1)
Index Cond: ((record_insert_datetime >= '2016-01-01
00:00:00'::timestamp without time zone) AND (record_insert_datetime <=
'2020-12-31 00:00:00'::timestamp without time zone))
-> Index Scan using record_insert_datetime_2017_idx on
contact_transaction_history_year_2017 (cost=0.12..2.34 rows=1 width=13934)
(actual time=0.014..0.014 rows=0 loops=1)
Index Cond: ((record_insert_datetime >= '2016-01-01
00:00:00'::timestamp without time zone) AND (record_insert_datetime <=
'2020-12-31 00:00:00'::timestamp without time zone))
-> Index Scan using record_insert_datetime_2018_idx on
contact_transaction_history_year_2018 (cost=0.12..2.34 rows=1 width=13934)
(actual time=0.008..0.008 rows=0 loops=1)
Index Cond: ((record_insert_datetime >= '2016-01-01
00:00:00'::timestamp without time zone) AND (record_insert_datetime <=
'2020-12-31 00:00:00'::timestamp without time zone))
-> Index Scan using record_insert_datetime_2019_idx on
contact_transaction_history_year_2019 (cost=0.12..2.34 rows=1 width=13934)
(actual time=0.005..0.005 rows=0 loops=1)
Index Cond: ((record_insert_datetime >= '2016-01-01
00:00:00'::timestamp without time zone) AND (record_insert_datetime <=
'2020-12-31 00:00:00'::timestamp without time zone))
-> Index Scan using record_insert_datetime_2020_idx on
contact_transaction_history_year_2020 (cost=0.12..2.34 rows=1 width=13934)
(actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((record_insert_datetime >= '2016-01-01
00:00:00'::timestamp without time zone) AND (record_insert_datetime <=
'2020-12-31 00:00:00'::timestamp without time zone))
Planning time: 0.751 ms
Execution time: 110.681 ms
*before partition table #*
explain analyze select * from core.contact_transaction_history_oldtoday
where record_insert_datetime >= '2016-01-01' and record_insert_datetime
<='2020-12-31'
Seq Scan on contact_transaction_history_oldtoday ctho (cost=0.00..14688.41
rows=313493 width=1707) (actual time=0.006..74.934 rows=313494 loops=1)
Filter: ((record_insert_datetime >= '2016-01-01 00:00:00'::timestamp
without time zone) AND (record_insert_datetime <= '2020-12-31
00:00:00'::timestamp without time zone))
Planning time: 0.448 ms
Execution time: 86.657 ms
*I see the partitioned table is taking more planning and execution than
non-partition tables. This is surprising for me. I need someone's help if I
am missing something or I am understanding in a different way. *
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani *(OCP12c/11g/10g/9i, AWS SAA, M103)*
*Dubai** , UAE* *| BLR , INDIA*
M: *+971 54 723 0075 *| *+91 90086 70302 | *WApp :* +91 81975 99922*
W: https://dbatocloudsolution.blogspot.in/
<http://dbatocloudsolution.blogspot.in/; | E: dbatocloud17@gmail.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Partition table Creation and Testing using Inherited method
@ 2021-03-03 08:48 Laurenz Albe <laurenz.albe@cybertec.at>
parent: dbatoCloud Solution <dbatocloud17@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2021-03-03 08:48 UTC (permalink / raw)
To: dbatoCloud Solution <dbatocloud17@gmail.com>; pgsql-admin@lists.postgresql.org
On Wed, 2021-03-03 at 08:49 +0530, dbatoCloud Solution wrote:
> Someone guide me on partitioning, I used the declarative method( inheritance),
> Created child tables, added check constraints and steps below which I mentioned it.
>
> step1 #
> CREATE TABLE core.contact_transaction_history (
>
> record_insert_datetime timestamp NULL,
> crm_contact_type_id int4 NULL
> );
>
> step2#
> CREATE TABLE core.contact_transaction_history_Year_2016() INHERITS (core.contact_transaction_history_PartMar2021);
> CREATE TABLE core.contact_transaction_history_Year_2017() INHERITS (core.contact_transaction_history_PartMar2021);
> CREATE TABLE core.contact_transaction_history_Year_2018() INHERITS (core.contact_transaction_history_PartMar2021);
Don't use inheritance partitioning.
Upgrade to a recent PostgreSQL release and use declarative partitioning.
It is so much better when it comes to performance and maintenance costs.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Partition table Creation and Testing using Inherited method
@ 2021-03-03 09:01 dbatoCloud Solution <dbatocloud17@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: dbatoCloud Solution @ 2021-03-03 09:01 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
Dear Laurenz,
Thanks! I understood the problem, But, at this moment it is not possible to
upgrade the DB version (10.13) to 11 or 12 due to a sprint issue.
Is there any other way to improve the performance?
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani *(OCP12c/11g/10g/9i, AWS SAA, M103)*
*Dubai** , UAE* *| BLR , INDIA*
M: *+971 54 723 0075 *| *+91 90086 70302 | *WApp :* +91 81975 99922*
W: https://dbatocloudsolution.blogspot.in/
<http://dbatocloudsolution.blogspot.in/; | E: dbatocloud17@gmail.com
On Wed, Mar 3, 2021 at 2:18 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Wed, 2021-03-03 at 08:49 +0530, dbatoCloud Solution wrote:
> > Someone guide me on partitioning, I used the declarative method(
> inheritance),
> > Created child tables, added check constraints and steps below which I
> mentioned it.
> >
> > step1 #
> > CREATE TABLE core.contact_transaction_history (
> >
> > record_insert_datetime timestamp NULL,
> > crm_contact_type_id int4 NULL
> > );
> >
> > step2#
> > CREATE TABLE core.contact_transaction_history_Year_2016() INHERITS
> (core.contact_transaction_history_PartMar2021);
> > CREATE TABLE core.contact_transaction_history_Year_2017() INHERITS
> (core.contact_transaction_history_PartMar2021);
> > CREATE TABLE core.contact_transaction_history_Year_2018() INHERITS
> (core.contact_transaction_history_PartMar2021);
>
> Don't use inheritance partitioning.
>
> Upgrade to a recent PostgreSQL release and use declarative partitioning.
>
> It is so much better when it comes to performance and maintenance costs.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Partition table Creation and Testing using Inherited method
@ 2021-03-03 11:04 Laurenz Albe <laurenz.albe@cybertec.at>
parent: dbatoCloud Solution <dbatocloud17@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2021-03-03 11:04 UTC (permalink / raw)
To: dbatoCloud Solution <dbatocloud17@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Wed, 2021-03-03 at 14:31 +0530, dbatoCloud Solution wrote:
> Thanks! I understood the problem, But, at this moment it is not possible to
> upgrade the DB version (10.13) to 11 or 12 due to a sprint issue.
PostgreSQL v10 already has (limited) partitioning support, but I'd
rather use that than inheritance partitioning.
> Is there any other way to improve the performance?
Partitioning typically doesn't improve query performance...
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Partition table Creation and Testing using Inherited method
@ 2021-03-03 11:36 dbatoCloud Solution <dbatocloud17@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: dbatoCloud Solution @ 2021-03-03 11:36 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
I dropped the table and recreated the partition table by using the Range by
the condition. Regarding performance improvement, I am working on it like
creating a separate index for sub-partition also parallel process if it is
possible
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani *(OCP12c/11g/10g/9i, AWS SAA, M103)*
*Dubai** , UAE* *| BLR , INDIA*
M: *+971 54 723 0075 *| *+91 90086 70302 | *WApp :* +91 81975 99922*
W: https://dbatocloudsolution.blogspot.in/
<http://dbatocloudsolution.blogspot.in/; | E: dbatocloud17@gmail.com
On Wed, Mar 3, 2021 at 4:34 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Wed, 2021-03-03 at 14:31 +0530, dbatoCloud Solution wrote:
> > Thanks! I understood the problem, But, at this moment it is not possible
> to
> > upgrade the DB version (10.13) to 11 or 12 due to a sprint issue.
>
> PostgreSQL v10 already has (limited) partitioning support, but I'd
> rather use that than inheritance partitioning.
>
> > Is there any other way to improve the performance?
>
> Partitioning typically doesn't improve query performance...
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-03-03 11:36 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-03 03:19 Partition table Creation and Testing using Inherited method dbatoCloud Solution <dbatocloud17@gmail.com>
2021-03-03 08:48 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-03 09:01 ` dbatoCloud Solution <dbatocloud17@gmail.com>
2021-03-03 11:04 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-03-03 11:36 ` dbatoCloud Solution <dbatocloud17@gmail.com>
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