We have a cloud sql postgres installation with 16vcpu . Suddenly , there is an increase in storage size related to indexes. The table size is 20gb and 4 indexes in it are consuming 70gb+ as per pgadmin stats. Table has a bulk delete and insert ops in transactions.
Is there any special flags that needs to be added ?
PS: I heard about WAL retention settings but not sure whether it will have any impact and I cannot actually ssh to the machine as it a GCP SQL.
[Update 1]: After deleting table/indexes, re-created:
Table T1 Stats :
Sequential scans 911504
Sequential tuples read 7338381399896
Index scans 173853066
Index tuples fetched 98226419759
Tuples inserted 109307632
Tuples updated 804
Tuples deleted 100410620
Tuples HOT updated 0
Live tuples 8797115
Dead tuples 24503371
Heap blocks read 1829681
Heap blocks hit 287184313804
Index blocks read 11094719
Index blocks hit 4239641531
Toast blocks read 0
Toast blocks hit 0
Toast index blocks read 0
Toast index blocks hit 0
Last vacuum
Last autovacuum 2020-03-31 18:48:45.626151+00
Last analyze 2020-03-31 14:17:20.834182+00
Last autoanalyze 2020-03-31 18:48:53.828135+00
Vacuum counter 0
Autovacuum counter 24
Analyze counter 3
Autoanalyze counter 53
Table size 5726 MB
Toast table size 8192
Indexes size 21 GB
T1 pg_stat_user_indexes
indexrelname idx_scan idx_tup_read idx_tup_fetch Size
index1 0 0 0 4608 kB
index2 21 103,913,145 0 3417 MB
index3 2,786 1,110,430 135,322 4007 MB
index4 949,981 1,284,602 794,130 4020 MB
index5 7,549,112 1,043,077,414 1,043,060,187 1860 MB
index6 165,334,371 13,962,773,344 12,209,134,553 1692 MB
Table Struct - (Total 14 Col)
Col1 character varying
Col2 character varying
Col3 character varying
Col4 character varying
Col5 character varying
Col6 character varying
Col7 character varying
Col8 timestamp without time zone
Col9 timestamp without time zone
Index Definition - index1 and index4 are unique index
index1 Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
where Col8 is null
index2 Col5 Col6
index3 Col1 Col2 Col3 Col5 Col6
index4 Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9
where Col8 is not null
index5 Col4 Col5
index6 Col1 Col2 Col3 Col4 Col5
One strange thing I noticed while extracting stats is pg_stat_user_indexes have two additional indexes7&8 which are not visible under schemaname->tablename> indexes