Início / user-231136

Nikita Glukhov's questions

Martin Hope
Nikita Glukhov
Asked: 2023-12-16 09:19:23 +0800 CST
  • 6

Eu tenho uma consulta:

WITH route_ids_filtered_by_shipments AS (
    SELECT DISTINCT
        rts.route_id
    FROM
        route_to_shipment rts
    JOIN
        shipment s
            ON rts.shipment_id = s.shipment_id
    WHERE
        s.store_sender_id = ANY('{"a2342659-5f2f-11eb-85a3-1c34dae33151","7955ab25-0511-11ee-885e-08c0eb32014b","319ce173-2614-11ee-b10a-08c0eb31fffb","4bdddeb3-5ec9-11ee-b10a-08c0eb31fffb","8e6054c5-6db3-11ea-9786-0050560307be","485dc39c-debc-11ed-885e-08c0eb32014b","217d0f7b-78de-11ea-a214-0050560307be","a5a8a21a-9b9a-11ec-b0fc-08c0eb31fffb","79e7d5be-ef8b-11eb-a0ee-ec0d9a21b021","3f35d68a-1212-11ec-85ad-1c34dae33151","087bcf22-5f30-11eb-85a3-1c34dae33151","c065e1c8-a679-11eb-85a9-1c34dae33151"}'::uuid[])
)
SELECT
    r.acceptance_status
,   count(*) count
FROM
    route r
JOIN
    route_ids_filtered_by_shipments rifs
        ON r.route_id = rifs.route_id
WHERE
    r.acceptance_status <> 'ERRORED'::route_acceptance_status
GROUP BY
    r.acceptance_status;

Seu plano de execução (obtido via EXPLAIN (ANALYZE, BUFFERS, SETTINGS):

HashAggregate  (cost=579359.05..579359.09 rows=4 width=12) (actual time=6233.281..6669.596 rows=3 loops=1)
  Group Key: r.acceptance_status
  Batches: 1  Memory Usage: 24kB
  Buffers: shared hit=14075979 read=573570
  I/O Timings: shared/local read=19689.039
  ->  Hash Join  (cost=564249.11..578426.89 rows=186432 width=4) (actual time=6064.176..6658.862 rows=69460 loops=1)
        Hash Cond: (r.route_id = rts.route_id)
        Buffers: shared hit=14075979 read=573570
        I/O Timings: shared/local read=19689.039
        ->  Seq Scan on route r  (cost=0.00..13526.16 rows=248230 width=20) (actual time=0.015..112.580 rows=248244 loops=1)
              Filter: (acceptance_status <> 'ERRORED'::route_acceptance_status)
              Rows Removed by Filter: 7879
              Buffers: shared hit=5112 read=3492
              I/O Timings: shared/local read=35.687
        ->  Hash  (cost=561844.75..561844.75 rows=192349 width=16) (actual time=6063.413..6499.725 rows=69460 loops=1)
              Buckets: 262144  Batches: 1  Memory Usage: 5304kB
              Buffers: shared hit=14070867 read=570078
              I/O Timings: shared/local read=19653.352
              ->  HashAggregate  (cost=557997.77..559921.26 rows=192349 width=16) (actual time=6038.518..6487.332 rows=69460 loops=1)
                    Group Key: rts.route_id
                    Batches: 1  Memory Usage: 10257kB
                    Buffers: shared hit=14070867 read=570078
                    I/O Timings: shared/local read=19653.352
                    ->  Gather  (cost=1001.02..555707.18 rows=916234 width=16) (actual time=0.976..6341.587 rows=888024 loops=1)
                          Workers Planned: 7
                          Workers Launched: 7
                          Buffers: shared hit=14070867 read=570078
                          I/O Timings: shared/local read=19653.352
                          ->  Nested Loop  (cost=1.02..463083.78 rows=130891 width=16) (actual time=1.576..5990.903 rows=111003 loops=8)
                                Buffers: shared hit=14070867 read=570078
                                I/O Timings: shared/local read=19653.352
                                ->  Parallel Index Only Scan using route_to_shipment_pkey on route_to_shipment rts  (cost=0.56..78746.01 rows=517565 width=32) (actual time=0.050..733.728 rows=452894 loops=8)
                                      Heap Fetches: 401042
                                      Buffers: shared hit=94576 read=38851
                                      I/O Timings: shared/local read=2255.435
                                ->  Index Scan using shipment_pkey on shipment s  (cost=0.46..0.74 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=3623151)
                                      Index Cond: (shipment_id = rts.shipment_id)
"                                      Filter: (store_sender_id = ANY ('{a2342659-5f2f-11eb-85a3-1c34dae33151,7955ab25-0511-11ee-885e-08c0eb32014b,319ce173-2614-11ee-b10a-08c0eb31fffb,4bdddeb3-5ec9-11ee-b10a-08c0eb31fffb,8e6054c5-6db3-11ea-9786-0050560307be,485dc39c-debc-11ed-885e-08c0eb32014b,217d0f7b-78de-11ea-a214-0050560307be,a5a8a21a-9b9a-11ec-b0fc-08c0eb31fffb,79e7d5be-ef8b-11eb-a0ee-ec0d9a21b021,3f35d68a-1212-11ec-85ad-1c34dae33151,087bcf22-5f30-11eb-85a3-1c34dae33151,c065e1c8-a679-11eb-85a9-1c34dae33151}'::uuid[]))"
                                      Rows Removed by Filter: 1
                                      Buffers: shared hit=13976291 read=531227
                                      I/O Timings: shared/local read=17397.917
"Settings: effective_cache_size = '256GB', effective_io_concurrency = '250', max_parallel_workers = '24', max_parallel_workers_per_gather = '8', random_page_cost = '1', seq_page_cost = '1.2', work_mem = '128MB'"
Planning:
  Buffers: shared hit=16
Planning Time: 0.409 ms
Execution Time: 6670.976 ms

Minha tarefa é fazer com que a consulta seja executada em pelo menos 1 segundo. Posso observar no plano (com base no meu conhecimento atual sobre otimização de consultas PG) que alguns nós têm um grande número de buscas de heap e isso pode ser curado com VACCUM em uma tabela. O que estou tentando compreender:

  1. Por que o PG escolheu ONo predicado de junção rts.shipment_id = shipment_idcomo base para a construção de um conjunto de linhas e realizou a filtragem store_sender_iddesse conjunto se houver um índice separado na shipment.store_sender_idcoluna que seja altamente seletivo. No meu entendimento, encontrar um número relativamente pequeno de linhas correspondentes store_sender_ide filtrar rts.shipment_id = shipment_idseria muito mais rápido. Ou pode haver união de varreduras de índice de bitmap (via BitmapAnd).

  2. Varredura de índice usandoshipping_pkey em remessas (custo=0,46..0,74 linhas=1 largura=16) (tempo real=0,011..0.011 linhas=0 loops=3623151)

Se eu multiplicar o tempo total real pelo loopscontador para obter o tempo real, chego perto de 40 segundos, quando a consulta é concluída em 7 segundos. Como isso poderia ser???

postgresql
Martin Hope
Nikita Glukhov
Asked: 2023-12-10 13:30:58 +0800 CST
  • 6

Como funciona o predicado ON do Postgres LATERAL JOIN?

Deixe-me esclarecer um pouco a questão. Eu li a documentação oficial e vários artigos sobre esse tipo de JOIN. Pelo que entendi é um loop foreach com uma subconsulta correlacionada dentro - ele itera sobre todos os registros de uma tabela A, permitindo referenciar colunas de uma linha "atual" em uma subconsulta correlacionada B e juntar um conjunto de resultados do B para aquela linha "atual" de A - se a consulta B retornar 1 linha, haverá apenas um par, e se a consulta B retornar N linhas, haverá N pares com a linha "atual" duplicada de A. O mesmo comportamento dos JOINs usuais .

Mas por que há necessidade do predicado ON? Para mim, em JOINs usuais usamos ON porque temos um produto cartesiano de 2 tabelas a ser filtrado, e não é o caso de LATERAL JOIN, que produz pares resultantes diretamente. Em outras palavras, na minha experiência de desenvolvedor eu só vi CROSS JOIN LATERAL e LEFT JOIN LATERAL () ON TRUE (este último parece bastante desajeitado), mas um dia um colega me mostrou

SELECT
r.acceptance_status, count(*) as count
FROM route r
LEFT JOIN LATERAL (
    SELECT rts.route_id, array_agg(rts.shipment_id) shipment_ids
    FROM route_to_shipment rts
    where rts.route_id = r.route_id
    GROUP BY rts.route_id
) rts using (route_id)

e isso explodiu minha mente. Por que using (route_id)? Já temos where rts.route_id = r.route_iddentro da subconsulta!!! Talvez eu tenha entendido errado a mecânica das junções LATERAL?

postgresql
Martin Hope
Nikita Glukhov
Asked: 2021-06-04 13:13:43 +0800 CST
  • 3

Eu tenho um ambiente típico Java Spring + Postgres (o projeto é legado). Uma camada de persistência de backend tem uma mistura de níveis de isolamento declarados - alguns deles são o padrão, ou seja, READ COMMITED, outros são REPEATABLE READ e alguns são SERIALIZABLE.

Às vezes, as mesmas tabelas de banco de dados são acessadas de transações paralelas com diferentes níveis de isolamento.

Existem algumas regras rígidas para as interações dessas transações?

Eu posso, mais ou menos, entender a situação, quando todas as transações têm o mesmo nível de isolamento e algumas delas declaram bloqueios explícitos para evitar fenômenos de leitura indesejáveis, mas não o caso descrito acima.

postgresql isolation-level