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

От: Andrus
Тема: Re: Hash join on int takes 8..114 seconds
Дата: ,
Msg-id: E9454EA8603E4D20B40EB9B0AB5A307C@andrusnotebook
(см: обсуждение, исходный текст)
Ответ на: Re: Hash join on int takes 8..114 seconds  (Richard Huxton)
Ответы: Re: Hash join on int takes 8..114 seconds  (Scott Carey)
Список: pgsql-performance

Скрыть дерево обсуждения

Hash join on int takes 8..114 seconds  ("Andrus", )
 Re: Hash join on int takes 8..114 seconds  (PFC, )
  Re: Hash join on int takes 8..114 seconds  ("Andrus", )
 Re: Hash join on int takes 8..114 seconds  (Richard Huxton, )
  Re: Hash join on int takes 8..114 seconds  ("Andrus", )
   Re: Hash join on int takes 8..114 seconds  (Richard Huxton, )
    Re: Hash join on int takes 8..114 seconds  ("Andrus", )
   Re: Hash join on int takes 8..114 seconds  (PFC, )
    Re: Hash join on int takes 8..114 seconds  ("Andrus", )
     Re: Hash join on int takes 8..114 seconds  (, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
       Re: Hash join on int takes 8..114 seconds  (Alan Hodgson, )
       Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
     Re: Hash join on int takes 8..114 seconds  (Richard Huxton, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
        Re: Hash join on int takes 8..114 seconds  ("Andrus", )
         Re: Hash join on int takes 8..114 seconds  (Alvaro Herrera, )
          Re: Hash join on int takes 8..114 seconds  ("Andrus", )
           Re: Hash join on int takes 8..114 seconds  (Alvaro Herrera, )
            Re: Hash join on int takes 8..114 seconds  ("Andrus", )
             Re: Hash join on int takes 8..114 seconds  (Alvaro Herrera, )
           Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (Scott Carey, )
     Re: Hash join on int takes 8..114 seconds  (PFC, )
      Re: Hash join on int takes 8..114 seconds  (Tom Lane, )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
      Re: Hash join on int takes 8..114 seconds  ("Andrus", )
       Re: Hash join on int takes 8..114 seconds  (PFC, )
        Re: Hash join on int takes 8..114 seconds  ("Andrus", )
         Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
          Re: Hash join on int takes 8..114 seconds  ("Andrus", )
           Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
         Re: Hash join on int takes 8..114 seconds  (Scott Carey, )
          Re: Hash join on int takes 8..114 seconds  ("Andrus", )
           Re: Hash join on int takes 8..114 seconds  (Tomas Vondra, )
            Re: Hash join on int takes 8..114 seconds  ("Andrus", )
             Re: Hash join on int takes 8..114 seconds  (, )
              Re: Hash join on int takes 8..114 seconds  ("Andrus", )

> 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





В списке pgsql-performance по дате сообщения:

От: Scott Carey
Дата:
Сообщение: Re: Hash join on int takes 8..114 seconds
От: Glyn Astill
Дата:
Сообщение: Perc 3 DC