On 03/12/2018 10:48 PM, Nikolay Samokhvalov wrote:
On Tue, Mar 13, 2018 at 1:28 AM, Ron Johnson <ron.l.john...@cox.net
<mailto:ron.l.john...@cox.net>> wrote:
On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote:
On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson <ron.l.john...@cox.net
<mailto:ron.l.john...@cox.net>> wrote:
v8.4.12
This is *very* old version, not supported by the community for many
years. Check https://www.postgresql.org/ to seecurrentlysupported
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()
