Re: Hash join on int takes 8..114 seconds

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

 



If it's not a million rows, then the table is bloated. Try (as postgres
or some other db superuser) "vacuum full pg_shdepend" and a "reindex
pg_shdepend".

reindex table pg_shdepend causes error

ERROR:  shared table "pg_shdepend" can only be reindexed in stand-alone mode

vacuum full verbose pg_shdepend seems to work but indexes are still bloated.
How to remove index bloat ?

sizes after vacuum full are below.
pg_shdepend size 1234 MB includes its index sizes, so indexes are 100% bloated.

     4         1214 pg_catalog.pg_shdepend                        1234 MB
     6         1232 pg_catalog.pg_shdepend_depender_index         795 MB
     7         1233 pg_catalog.pg_shdepend_reference_index        439 MB

Andrus.


vacuum full verbose pg_shdepend;

INFO:  vacuuming "pg_catalog.pg_shdepend"
INFO: "pg_shdepend": found 254 removable, 3625 nonremovable row versions in 131517 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 49 to 49 bytes long.
There were 16115259 unused item pointers.
Total free space (including removable row versions) is 1010091872 bytes.
131456 pages are or will become empty, including 8 at the end of the table.
131509 pages containing 1010029072 free bytes are potential move destinations.
CPU 2.08s/0.92u sec elapsed 63.51 sec.
INFO: index "pg_shdepend_depender_index" now contains 3625 row versions in 101794 pages
DETAIL:  254 index row versions were removed.
101611 index pages have been deleted, 20000 are currently reusable.
CPU 0.87s/0.28u sec elapsed 25.44 sec.
INFO: index "pg_shdepend_reference_index" now contains 3625 row versions in 56139 pages
DETAIL:  254 index row versions were removed.
56076 index pages have been deleted, 20000 are currently reusable.
CPU 0.51s/0.15u sec elapsed 23.10 sec.
INFO:  "pg_shdepend": moved 1518 row versions, truncated 131517 to 25 pages
DETAIL:  CPU 5.26s/2.39u sec elapsed 89.93 sec.
INFO: index "pg_shdepend_depender_index" now contains 3625 row versions in 101794 pages
DETAIL:  1518 index row versions were removed.
101609 index pages have been deleted, 20000 are currently reusable.
CPU 0.94s/0.28u sec elapsed 24.61 sec.
INFO: index "pg_shdepend_reference_index" now contains 3625 row versions in 56139 pages
DETAIL:  1518 index row versions were removed.
56088 index pages have been deleted, 20000 are currently reusable.
CPU 0.54s/0.14u sec elapsed 21.11 sec.

Query returned successfully with no result in 253356 ms




--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux