Search Postgresql Archives

Re: Reindex doesn't eliminate bloat

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 

On 03/12/2018 10:48 PM, Nikolay Samokhvalov wrote:
On Tue, Mar 13, 2018 at 1:28 AM, Ron Johnson <ron.l.johnson@xxxxxxx> wrote:
On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote:
On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson <ron.l.johnson@xxxxxxx> wrote:
v8.4.12
 
This is *very* old version, not supported by the community for many years. Check https://www.postgresql.org/ to see currently supported versions.
You need to upgrade it.

Don't even think I'm in control of when -- or even if -- the customer decides to upgrade.

That being the case, do you have an answer to the question? 

Those queries from wiki for table and index bloat estimation are for estimation only. In many cases they show very wrong results. Better (yet not ideal) approach is using pgstattuple extension (though I'm not sure it existed back in 2009).

Can you provide table and index definition and, if you can, some sample data?

Sadly, no sample data.  (It's all PCI controlled.)

Index idx_item_mapping_rp7_y2016m03itemmapping_custom_userfield_801 has 40% bloat.

Thanks.

--
Angular momentum makes the world go 'round.
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()
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux