Início / user-124741

gsa's questions

Martin Hope
gsa
Asked: 2023-08-30 01:05:54 +0800 CST
  • 6

Eu tenho um sistema legado que usa Posgresql 9.6e Ubuntu 16.04. Tudo estava bem há vários dias, mesmo com as configurações padrão do Postgresql. Descartei um banco de dados com a opção de compactação (nível máximo de compactação -Z 9) para ter um tamanho menor ( pg_dump --compress=9 database_name > database_name.sql). Depois disso tive muitos problemas. Algumas consultas para determinadas tabelas passaram a ser executadas de forma muito lenta. Consultas para outras tabelas funcionam bem.

Aqui estão as tabelas com as quais tenho problemas.

isbns:
id (integer)
value (string), index b-tree
type (string)



books:
id (integer)
isbn (string), index b-tree
...
(total 32 columns)



isbns_statistics:
id (integer)
average_price (float)
average_rating (integer)
isbn_id (foreign key)
...
(total 17 columns)

Essas tabelas contêm 1.400.000 linhas cada.

Basicamente usei a seguinte consulta e funcionou bem:

(1) SELECT * FROM ISBNS JOIN BOOKS ON BOOKS.ISBN = ISBN.VALUE JOIN ISBNS_STATISTICS ON ISBNS_STATISTICS.ISBN_ID = ISBNS.ID ORDER BY ISBNS.VALUE LIMIT 100;

Mas depois que fiz o dump ele começou a ser executado de forma extremamente lenta. Não tenho certeza se é por causa do despejo, mas antes do despejo tudo funcionava bem. Esta consulta também funciona bem:

SELECT * FROM ISBNS JOIN BOOKS ON BOOKS.ISBN = ISBN.VALUE JOIN ISBNS_STATISTICS ON ISBNS_STATISTICS.ISBN_ID = ISBNS.ID LIMIT 100;

Esta consulta também é executada rapidamente:

SELECT * FROM ISBNS JOIN BOOKS ON BOOKS.ISBN = ISBN.VALUE ORDER BY ISBNS.VALUE LIMIT 100;

Alterei as configurações de desempenho (por exemplo, aumentei shared_buffer), mas isso não aumentou muito a velocidade.

Eu li que consultas contendo LIMIT e ORDER BY funcionam muito lentamente, mas se eu fizer essas consultas em outras tabelas, funciona bem.

Aqui está o plano de consulta para consulta (1):

                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1048379.37..1048428.33 rows=100 width=498) (actual time=5264.193..5264.444 rows=100 loops=1)
   Buffers: shared hit=40250 read=332472, temp read=16699 written=28392
   ->  Merge Join  (cost=1048379.37..2291557.51 rows=2539360 width=498) (actual time=5264.191..5264.436 rows=100 loops=1)
         Merge Cond: ((books.isbn)::text = (isbns.value)::text)
         Buffers: shared hit=40250 read=332472, temp read=16699 written=28392
         ->  Index Scan using books_isbn_key on books  (cost=0.43..1205494.88 rows=1386114 width=333) (actual time=0.020..0.150 rows=100 loops=1)
               Buffers: shared hit=103
         ->  Materialize  (cost=1042333.77..1055199.75 rows=2573197 width=155) (actual time=5263.901..5263.960 rows=100 loops=1)
               Buffers: shared hit=40147 read=332472, temp read=16699 written=28392
               ->  Sort  (cost=1042333.77..1048766.76 rows=2573197 width=155) (actual time=5263.895..5263.949 rows=100 loops=1)
                     Sort Key: isbns.value
                     Sort Method: external merge  Disk: 136864kB
                     Buffers: shared hit=40147 read=332472, temp read=16699 written=28392
                     ->  Hash Join  (cost=55734.14..566061.44 rows=2573197 width=155) (actual time=403.962..1994.884 rows=1404582 loops=1)
                           Hash Cond: (isbns_statistics.isbn_id = isbns.id)
                           Buffers: shared hit=40147 read=332472, temp read=11281 written=11279
                           ->  Seq Scan on isbns_statistics  (cost=0.00..385193.97 rows=2573197 width=120) (actual time=0.024..779.717 rows=1404582 loops=1)
                                 Buffers: shared hit=26990 read=332472
                           ->  Hash  (cost=27202.84..27202.84 rows=1404584 width=35) (actual time=402.431..402.431 rows=1404584 loops=1)
                                 Buckets: 1048576  Batches: 2  Memory Usage: 51393kB
                                 Buffers: shared hit=13157, temp written=4363
                                 ->  Seq Scan on isbns  (cost=0.00..27202.84 rows=1404584 width=35) (actual time=0.027..152.568 rows=1404584 loops=1)
                                       Buffers: shared hit=13157
 Planning time: 1.160 ms
 Execution time: 5279.983 ms
(25 rows)

Então, as perguntas são:

  1. Por que tudo funcionou bem e começou a funcionar lentamente?
  2. Por que consultas semelhantes a outras tabelas ainda são executadas rapidamente?
postgresql