Deepak Mahto: Maximizing PostgreSQL’s Charms: A Pattern for Indexes and Skewness When Migrating from Oracle

Introduction Migrating from Oracle to PostgreSQL can be a massive undertaking, given the functionalities and complexities involved across various areas of the database. Oracle has its own depth of features, usage patterns, and proprietary add-ons, while PostgreSQL has its own unique charms and advantages in every way. In this blog post, we will explore a pattern for combining indexes and statistics, and enhancing them with PostgreSQL features. We will also examine areas for further optimization by considering migration as an opportunity. Let’s consider a sample table in Oracle with some columns having skewness and with histogram enabled. Histograms can aid in determining when to switch table access patterns based on the underlying data and filters provided. Oracle Database – sample skew table An Oracle table named TABLESKEW has been created with indexes and histograms measured on specific columns. If you’re interested in exploring the METHOD_OPT option in Oracle further, check out this resource. CREATE TABLE TABLESKEW AS SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED FROM DBA_OBJECTS; Begin Dbms_Stats.Gather_Table_Stats( 'MOCKSCHEMA', 'TABLESKEW', Estimate_Percent => 100, Cascade => True, Method_Opt => 'FOR COLUMNS SIZE 256 OBJECT_TYPE STATUS TEMPORARY GENERATED' ); END; / create index idx1 on TABLESKEW(STATUS); create index idx2 on TABLESKEW(TEMPORARY); create index idx3 on TABLESKEW(GENERATED); Below is a snapshot of the statistics for TABLESKEW, which displays columns with low cardinality and frequency-based histograms. Having a histogram with an index on a skewed column can facilitate efficient performance by making better decision with correct cardinality. SQL> EXPLAIN PLAN FOR select * from TABLESKEW where STATUS = 'INVALID'; select * from table(dbms_xplan.display()); Explained. SQL> PLAN_TABLE_O[...]