Partition table Creation and Testing using Inherited method

[dbatoCloud Solution <dbatocloud17@xxxxxxxxx>]

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/ | E: dbatocloud17@xxxxxxxxx