Partitioning - PostgreSQL Developer's Guide - page 119

Creating an index on child tables
Until now, we have created the master table and child tables inherited from the
master table. Now, we are going to create indexes on child tables to speed up the
sales_day ield usage, using almost all queries ( INSERT , SELECT , and UPDATE ) on the
date ield.
Create the m1_to_m2_sales_date index on the sales_record_m1_to_m2 child table
as follows:
warehouse_db=# CREATE INDEX m1_to_m2_sales_date ON
sales_record_m1_to_m2 (sales_date);
Create the m3_to_m4_sales_date index on the sales_record_m3_to_m4 child table
as follows:
warehouse_db=# CREATE INDEX m3_to_m4_sales_date ON
sales_record_m3_to_m4 (sales_date);
Now, let's create the m5_to_m6_sales_date index on the sales_record_m5_to_m6
child table:
warehouse_db=# CREATE INDEX m5_to_m6_sales_date ON
sales_record_m5_to_m6 (sales_date);
Create the m7_to_m8_sales_date index on the sales_record_m7_to_m8 child table
as follows:
warehouse_db=# CREATE INDEX m7_to_m8_sales_date ON
sales_record_m7_to_m8 (sales_date);
Then, create the m9_to_m10_sales_date index on the sales_record_m9_to_m10
child table as follows:
warehouse_db=# CREATE INDEX m9_to_m10_sales_date ON
sales_record_m9_to_m10 (sales_date);
Create the m11_to_m12_sales_date index on the sales_record_m11_to_m12 child
table as follows:
warehouse_db=# CREATE INDEX m11_to_m12_sales_date ON
sales_record_m11_to_m12 (sales_date);
Creating a trigger on the master table
The next thing that we need after the indexes is to create a trigger and the trigger
function to the master table. Conditions must be exactly the same as what the child
tables check.