Horton

bloated_index_table.txt

text/plain

Download
Filename: bloated_index_table.txt
Type: text/plain
Part: 0
Message: Re: Reindex doesn't eliminate bloat 
TAPd=# \d tms.item_mapping_rp7_y2013m03
                                               Table "tms.item_mapping_rp7_y2013m03"
         Column         |            Type             |                                 Modifiers                                  
------------------------+-----------------------------+----------------------------------------------------------------------------
 item_mapping_id        | integer                     | not null default nextval('tms.item_mapping_item_mapping_id_seq'::regclass)
 item_seq_no            | numeric(10,0)               | 
 account_no             | character varying(255)      | 
 amount                 | numeric(21,2)               | 
 gross_amount           | numeric(21,2)               | 
 net_amount             | numeric(21,2)               | 
 amount_3               | numeric(21,2)               | 
 custom_userfield_401   | character varying(255)      | 
 custom_userfield_402   | character varying(255)      | 
 custom_userfield_403   | character varying(255)      | 
 custom_userfield_404   | character varying(255)      | 
 custom_userfield_405   | character varying(255)      | 
 custom_userfield_416   | character varying(16)       | 
 custom_userfield_417   | character varying(16)       | 
 custom_userfield_418   | character varying(16)       | 
 custom_userfield_419   | character varying(16)       | 
 custom_userfield_420   | numeric(10,0)               | 
 custom_userfield_421   | numeric(10,0)               | 
 custom_userfield_422   | numeric(10,0)               | 
 custom_userfield_423   | numeric(21,2)               | 
 custom_userfield_424   | numeric(21,2)               | 
 custom_userfield_425   | numeric(21,2)               | 
 custom_userfield_426   | numeric(10,0)               | 
 custom_userfield_431   | character varying(16)       | 
 custom_userfield_432   | character varying(16)       | 
 custom_userfield_433   | character varying(16)       | 
 custom_userfield_434   | character varying(16)       | 
 custom_userfield_436   | character varying(16)       | 
 custom_userfield_437   | numeric(10,0)               | 
 custom_userfield_438   | numeric(10,0)               | 
 custom_userfield_439   | numeric(10,0)               | 
 custom_userfield_440   | numeric(10,0)               | 
 custom_userfield_441   | numeric(10,0)               | 
 custom_userfield_442   | numeric(10,0)               | 
 custom_userfield_443   | numeric(10,0)               | 
 custom_userfield_444   | character varying(1)        | 
 custom_userfield_445   | character varying(1)        | 
 custom_userfield_446   | character varying(1)        | 
 custom_userfield_447   | character varying(1)        | 
 custom_userfield_448   | character varying(1)        | 
 custom_userfield_449   | character varying(1)        | 
 custom_userfield_450   | character varying(1)        | 
 custom_userfield_451   | character varying(1)        | 
 custom_userfield_452   | character varying(1)        | 
 custom_userfield_453   | numeric(21,2)               | 
 custom_userfield_454   | numeric(21,2)               | 
 custom_userfield_455   | numeric(21,2)               | 
 custom_userfield_456   | numeric(21,2)               | 
 custom_userfield_457   | numeric(21,2)               | 
 custom_userfield_458   | numeric(21,2)               | 
 custom_userfield_459   | numeric(21,2)               | 
 custom_userfield_460   | numeric(21,2)               | 
 cds_invoice_seq_number | numeric(10,0)               | 
 created_on             | timestamp without time zone | not null
 created_by             | integer                     | 
 modified_on            | timestamp without time zone | 
 modified_by            | integer                     | 
 type_id                | numeric(8,0)                | 
 custom_userfield_801   | character varying(256)      | 
 custom_userfield_802   | character varying(256)      | 
 custom_userfield_803   | character varying(256)      | 
 custom_userfield_804   | character varying(256)      | 
 custom_userfield_805   | character varying(256)      | 
 custom_userfield_806   | character varying(256)      | 
 custom_userfield_807   | character varying(256)      | 
 custom_userfield_808   | character varying(256)      | 
 custom_userfield_809   | character varying(256)      | 
 custom_userfield_810   | character varying(256)      | 
 custom_userfield_811   | character varying(256)      | 
 custom_userfield_812   | character varying(256)      | 
 custom_userfield_813   | character varying(256)      | 
 custom_userfield_814   | character varying(256)      | 
 custom_userfield_815   | character varying(256)      | 
 custom_userfield_816   | character varying(256)      | 
 custom_userfield_817   | character varying(256)      | 
 custom_userfield_818   | character varying(256)      | 
 custom_userfield_819   | character varying(256)      | 
 custom_userfield_820   | character varying(256)      | 
 custom_userfield_821   | character varying(256)      | 
 custom_userfield_822   | character varying(256)      | 
 custom_userfield_823   | character varying(256)      | 
 custom_userfield_824   | character varying(256)      | 
 custom_userfield_825   | character varying(256)      | 
 custom_userfield_826   | character varying(256)      | 
 custom_userfield_827   | character varying(256)      | 
 custom_userfield_828   | character varying(256)      | 
 custom_userfield_829   | character varying(256)      | 
 custom_userfield_830   | character varying(256)      | 
 custom_userfield_831   | character varying(256)      | 
 custom_userfield_832   | character varying(256)      | 
 custom_userfield_833   | character varying(256)      | 
 custom_userfield_834   | character varying(256)      | 
 custom_userfield_835   | character varying(256)      | 
 custom_userfield_836   | character varying(256)      | 
 custom_userfield_837   | character varying(256)      | 
 custom_userfield_838   | character varying(256)      | 
 custom_userfield_839   | character varying(256)      | 
 custom_userfield_840   | character varying(256)      | 
 custom_userfield_841   | character varying(256)      | 
 custom_userfield_842   | character varying(256)      | 
 custom_userfield_843   | character varying(256)      | 
 custom_userfield_844   | character varying(256)      | 
 custom_userfield_845   | character varying(256)      | 
 custom_userfield_846   | character varying(256)      | 
 custom_userfield_847   | character varying(256)      | 
 custom_userfield_848   | character varying(256)      | 
 custom_userfield_849   | character varying(256)      | 
 custom_userfield_850   | character varying(256)      | 
 custom_userfield_851   | character varying(256)      | 
 custom_userfield_852   | character varying(256)      | 
 custom_userfield_853   | character varying(256)      | 
 custom_userfield_854   | character varying(256)      | 
 custom_userfield_855   | character varying(256)      | 
 custom_userfield_856   | character varying(256)      | 
 custom_userfield_857   | character varying(256)      | 
 custom_userfield_858   | character varying(256)      | 
 custom_userfield_859   | character varying(256)      | 
 custom_userfield_860   | character varying(256)      | 
 custom_userfield_861   | character varying(256)      | 
 custom_userfield_862   | character varying(256)      | 
 custom_userfield_863   | character varying(256)      | 
 custom_userfield_864   | character varying(256)      | 
 custom_userfield_865   | character varying(256)      | 
 custom_userfield_866   | character varying(256)      | 
 custom_userfield_867   | character varying(256)      | 
 custom_userfield_868   | character varying(256)      | 
 custom_userfield_869   | character varying(256)      | 
 custom_userfield_870   | character varying(256)      | 
 process_date           | date                        | 
 ems_edited             | boolean                     | 
 ems_inserted           | numeric(10,0)               | 
 partition_type         | numeric(10,0)               | 
 data_source_code       | numeric(10,0)               | 
 arc_account_no         | character varying(255)      | 
Indexes:
    "pk_tms_item_mapping_rp7_y2013m03" PRIMARY KEY, btree (item_mapping_id)
    "idx_item_mapping_rp7_y2013m03_amount_3" btree (amount_3)
    "idx_item_mapping_rp7_y2013m03_arc_account_no" btree (arc_account_no)
    "idx_item_mapping_rp7_y2013m03_cds_invoice_seq_number" btree (cds_invoice_seq_number)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_416" btree (custom_userfield_416)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_417" btree (custom_userfield_417)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_418" btree (custom_userfield_418)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_419" btree (custom_userfield_419)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_420" btree (custom_userfield_420)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_421" btree (custom_userfield_421)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_431" btree (custom_userfield_431)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_432" btree (custom_userfield_432)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_433" btree (custom_userfield_433)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_434" btree (custom_userfield_434)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_436" btree (custom_userfield_436)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_803" btree (custom_userfield_803)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_804" btree (custom_userfield_804)
    "idx_item_mapping_rp7_y2013m03_custom_userfield_805" btree (custom_userfield_805)
    "idx_item_mapping_rp7_y2013m03_gross_amount" btree (gross_amount)
    "idx_item_mapping_rp7_y2013m03_net_amount" btree (net_amount)
    "idx_item_mapping_rp7_y2013m03_type_id" btree (type_id)
    "idx_item_mapping_rp7_y2013m03custom_userfield_801" btree (custom_userfield_801)
    "idx_item_mapping_rp7_y2013m03custom_userfield_802" btree (custom_userfield_802)
    "idx_item_mapping_rp7_y2013m03itemmapping_accountno" btree (account_no)
    "idx_item_mapping_rp7_y2013m03itemmapping_amount" btree (amount)
    "idx_item_mapping_rp7_y2013m03itemmapping_itemseqno" btree (item_seq_no)
    "idx_tms_item_mapping_rp7_y2013m03_partition_type" btree (partition_type)
    "idx_tms_item_mapping_rp7_y2013m03_process_date" btree (process_date)
Check constraints:
    "tms_item_mapping_rp7_y2013m03_constraint" CHECK (process_date >= '2013-03-01'::date AND process_date < '2013-09-01'::date AND partition_type = 7::nu
meric)
Inherits: tms.item_mapping

TAPd=# 
TAPd=# 
TAPd=# \d tms.item_mapping
                                                     Table "tms.item_mapping"
         Column         |            Type             |                                 Modifiers                                  
------------------------+-----------------------------+----------------------------------------------------------------------------
 item_mapping_id        | integer                     | not null default nextval('tms.item_mapping_item_mapping_id_seq'::regclass)
 item_seq_no            | numeric(10,0)               | 
 account_no             | character varying(255)      | 
 amount                 | numeric(21,2)               | 
 gross_amount           | numeric(21,2)               | 
 net_amount             | numeric(21,2)               | 
 amount_3               | numeric(21,2)               | 
 custom_userfield_401   | character varying(255)      | 
 custom_userfield_402   | character varying(255)      | 
 custom_userfield_403   | character varying(255)      | 
 custom_userfield_404   | character varying(255)      | 
 custom_userfield_405   | character varying(255)      | 
 custom_userfield_416   | character varying(16)       | 
 custom_userfield_417   | character varying(16)       | 
 custom_userfield_418   | character varying(16)       | 
 custom_userfield_419   | character varying(16)       | 
 custom_userfield_420   | numeric(10,0)               | 
 custom_userfield_421   | numeric(10,0)               | 
 custom_userfield_422   | numeric(10,0)               | 
 custom_userfield_423   | numeric(21,2)               | 
 custom_userfield_424   | numeric(21,2)               | 
 custom_userfield_425   | numeric(21,2)               | 
 custom_userfield_426   | numeric(10,0)               | 
 custom_userfield_431   | character varying(16)       | 
 custom_userfield_432   | character varying(16)       | 
 custom_userfield_433   | character varying(16)       | 
 custom_userfield_434   | character varying(16)       | 
 custom_userfield_436   | character varying(16)       | 
 custom_userfield_437   | numeric(10,0)               | 
 custom_userfield_438   | numeric(10,0)               | 
 custom_userfield_439   | numeric(10,0)               | 
 custom_userfield_440   | numeric(10,0)               | 
 custom_userfield_441   | numeric(10,0)               | 
 custom_userfield_442   | numeric(10,0)               | 
 custom_userfield_443   | numeric(10,0)               | 
 custom_userfield_444   | character varying(1)        | 
 custom_userfield_445   | character varying(1)        | 
 custom_userfield_446   | character varying(1)        | 
 custom_userfield_447   | character varying(1)        | 
 custom_userfield_448   | character varying(1)        | 
 custom_userfield_449   | character varying(1)        | 
 custom_userfield_450   | character varying(1)        | 
 custom_userfield_451   | character varying(1)        | 
 custom_userfield_452   | character varying(1)        | 
 custom_userfield_453   | numeric(21,2)               | 
 custom_userfield_454   | numeric(21,2)               | 
 custom_userfield_455   | numeric(21,2)               | 
 custom_userfield_456   | numeric(21,2)               | 
 custom_userfield_457   | numeric(21,2)               | 
 custom_userfield_458   | numeric(21,2)               | 
 custom_userfield_459   | numeric(21,2)               | 
 custom_userfield_460   | numeric(21,2)               | 
 cds_invoice_seq_number | numeric(10,0)               | 
 created_on             | timestamp without time zone | not null
 created_by             | integer                     | 
 modified_on            | timestamp without time zone | 
 modified_by            | integer                     | 
 type_id                | numeric(8,0)                | 
 custom_userfield_801   | character varying(256)      | 
 custom_userfield_802   | character varying(256)      | 
 custom_userfield_803   | character varying(256)      | 
 custom_userfield_804   | character varying(256)      | 
 custom_userfield_805   | character varying(256)      | 
 custom_userfield_806   | character varying(256)      | 
 custom_userfield_807   | character varying(256)      | 
 custom_userfield_808   | character varying(256)      | 
 custom_userfield_809   | character varying(256)      | 
 custom_userfield_810   | character varying(256)      | 
 custom_userfield_811   | character varying(256)      | 
 custom_userfield_812   | character varying(256)      | 
 custom_userfield_813   | character varying(256)      | 
 custom_userfield_814   | character varying(256)      | 
 custom_userfield_815   | character varying(256)      | 
 custom_userfield_816   | character varying(256)      | 
 custom_userfield_817   | character varying(256)      | 
 custom_userfield_818   | character varying(256)      | 
 custom_userfield_819   | character varying(256)      | 
 custom_userfield_820   | character varying(256)      | 
 custom_userfield_821   | character varying(256)      | 
 custom_userfield_822   | character varying(256)      | 
 custom_userfield_823   | character varying(256)      | 
 custom_userfield_824   | character varying(256)      | 
 custom_userfield_825   | character varying(256)      | 
 custom_userfield_826   | character varying(256)      | 
 custom_userfield_827   | character varying(256)      | 
 custom_userfield_828   | character varying(256)      | 
 custom_userfield_829   | character varying(256)      | 
 custom_userfield_830   | character varying(256)      | 
 custom_userfield_831   | character varying(256)      | 
 custom_userfield_832   | character varying(256)      | 
 custom_userfield_833   | character varying(256)      | 
 custom_userfield_834   | character varying(256)      | 
 custom_userfield_835   | character varying(256)      | 
 custom_userfield_836   | character varying(256)      | 
 custom_userfield_837   | character varying(256)      | 
 custom_userfield_838   | character varying(256)      | 
 custom_userfield_839   | character varying(256)      | 
 custom_userfield_840   | character varying(256)      | 
 custom_userfield_841   | character varying(256)      | 
 custom_userfield_842   | character varying(256)      | 
 custom_userfield_843   | character varying(256)      | 
 custom_userfield_844   | character varying(256)      | 
 custom_userfield_845   | character varying(256)      | 
 custom_userfield_846   | character varying(256)      | 
 custom_userfield_847   | character varying(256)      | 
 custom_userfield_848   | character varying(256)      | 
 custom_userfield_849   | character varying(256)      | 
 custom_userfield_850   | character varying(256)      | 
 custom_userfield_851   | character varying(256)      | 
 custom_userfield_852   | character varying(256)      | 
 custom_userfield_853   | character varying(256)      | 
 custom_userfield_854   | character varying(256)      | 
 custom_userfield_855   | character varying(256)      | 
 custom_userfield_856   | character varying(256)      | 
 custom_userfield_857   | character varying(256)      | 
 custom_userfield_858   | character varying(256)      | 
 custom_userfield_859   | character varying(256)      | 
 custom_userfield_860   | character varying(256)      | 
 custom_userfield_861   | character varying(256)      | 
 custom_userfield_862   | character varying(256)      | 
 custom_userfield_863   | character varying(256)      | 
 custom_userfield_864   | character varying(256)      | 
 custom_userfield_865   | character varying(256)      | 
 custom_userfield_866   | character varying(256)      | 
 custom_userfield_867   | character varying(256)      | 
 custom_userfield_868   | character varying(256)      | 
 custom_userfield_869   | character varying(256)      | 
 custom_userfield_870   | character varying(256)      | 
 process_date           | date                        | 
 ems_edited             | boolean                     | 
 ems_inserted           | numeric(10,0)               | 
 partition_type         | numeric(10,0)               | 
 data_source_code       | numeric(10,0)               | 
 arc_account_no         | character varying(255)      | 
Indexes:
    "pk_transaction_item_mapping" PRIMARY KEY, btree (item_mapping_id)
Referenced by:
    TABLE "tms.check_item_mapping" CONSTRAINT "fk_checkitemmapping_itemmapping" FOREIGN KEY (check_item_mapping_id) REFERENCES tms.item_mapping(item_mapp
ing_id)
    TABLE "tms.doc_item_mapping" CONSTRAINT "fk_docitemmapping_itemmapping" FOREIGN KEY (doc_item_mapping_id) REFERENCES tms.item_mapping(item_mapping_id
)
Triggers:
    insert_item_mapping_trigger BEFORE INSERT ON tms.item_mapping FOR EACH ROW EXECUTE PROCEDURE tms.item_mapping_insert_function()