Partition table Creation and Testing using Inherited method

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(at)gmail(dot)com