dcc310's questions

Suponha que eu tenha uma tabela somente de acréscimo com colunas customer_id(string gerada aleatoriamente) e x, e as pesquisas são sempre feitas em customer_id.

Digamos que os dados se pareçam abaixo, como se tivéssemos um lote de linhas quando um cliente se inscreve para algo inicialmente e nunca mais para esse cliente.

customer_id=XCVFY0001, x=...
customer_id=XCVFY0001, x=...
(continues for ~1 page with same customer_id)
customer_id=HUMBN0001, x=...
customer_id=HUMBN0001, x=...
(continues for ~1 page with same customer_id)
(and so on...)

Portanto, embora customer_ida ordem alfabética de 's não esteja correlacionada com as linhas físicas, podemos fazer declarações como:

• há poucos IDs de cliente distintos por página
• há poucas páginas por ID
• há longas "execuções" de IDs ou, se você precisar de um customer_id, provavelmente o encontrará em algumas páginas contíguas
• em termos de teoria da informação, acho que eles diriam que não há correlação, mas há uma alta "informação mútua"

O planejador de consultas pode usar informações como essa em estimativas, se uma não for executada explicitamente CLUSTER? Eu suponho que, se houver baixo correlationconforme relatado em pg_stats, ele adivinharia que as linhas são distribuídas uniformemente pelas páginas e podem ser pessimistas com vários planos.

(No meu análogo do mundo real, um índice simples e não clusterizado tornou as coisas boas e rápidas de qualquer maneira, mas fiquei curioso quando notei o padrão nos dados.)

Mais uma pergunta da minha descoberta das ótimas novas EXPLAINopções do PostgreSQL. Este se concentra na BUFFERSopção.

Aqui está EXPLICAR :

EXPLAIN (ANALYZE, BUFFERS) SELECT event_time FROM ui_events_v2  WHERE page ~ 'foo' LIMIT 1;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..1539.68 rows=1 width=8) (actual time=0.858..0.858 rows=1 loops=1)
   Buffers: shared read=10
   I/O Timings: read=0.735
   ->  Seq Scan on ui_events_v2  (cost=0.00..3313394.58 rows=2152 width=8) (actual time=0.857..0.857 rows=1 loops=1)
         Filter: (page ~ 'foo'::text)
         Rows Removed by Filter: 112
         Buffers: shared read=10
         I/O Timings: read=0.735
 Planning Time: 6.455 ms
 Execution Time: 0.877 ms

É bastante rápido - e essa consulta é muito lenta em uma inicialização a frio. Esta é uma tabela de 30 milhões de linhas e sete linhas pagecontêm substring foo. A primeira linha que corresponde a esta consulta parece mostrar 604 mil páginas, o que seria ~20% das ~3 milhões de páginas mencionadas em pg_class:

SELECT min(ctid) FROM ui_events_v2 WHERE page ~ 'foo';
     min
-------------
 (604435,10)

Minha suposição era que, mesmo que cada página desta tabela estivesse no cache do PostgreSQL ou do sistema operacional, ela ainda precisaria percorrer uma lista linear de páginas para uma varredura sequencial. A seção Buffers: shared read=10e o 0.877 mstempo de execução me sugerem que de alguma forma está "pegando de onde parou" e começando em páginas que estão longe do início. Eu pensei que ele se movia através de IDs de página e espiava em um cache tentando cada página enquanto se movia, mas talvez comece a partir do próprio cache? Se for esse o caso, como ele descobre as páginas não armazenadas em cache?

Eu sei que não é garantido que as linhas sejam encontradas em nenhuma ordem específica, mas imaginei que, dentro da mesma estratégia de consulta, o caminho seguido seria relativamente semelhante.

Ao fazer uma pergunta recente , alguns componentes misteriosos de tempo de inicialização surgiram na minha saída EXPLAIN ANALYZE. Eu estava jogando mais e descobri que o tempo de inicialização cai para perto de 0 se eu remover a WHEREcláusula regex.

Eu executei o seguinte script bash como um teste:

for i in $(seq 1 10)
do
    if (( $RANDOM % 2 == 0 ))
    then
        echo "Doing plain count"
        psql -e -c "EXPLAIN ANALYZE SELECT count(*) FROM ui_events_v2"
    else
        echo "Doing regex count"
        psql -e -c "EXPLAIN ANALYZE SELECT count(*) FROM ui_events_v2 WHERE page ~ 'foo'"
    fi
done

A primeira consulta retorna uma contagem de ~30 milhões e a segunda conta apenas 7 linhas. Eles estão sendo executados em uma réplica de leitura PG 12.3 no RDS com o mínimo de outras atividades. Ambas as versões levam aproximadamente a mesma quantidade de tempo, como seria de esperar. Aqui está alguma saída filtrada com grep:

Doing plain count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060374.07 rows=12632507 width=0) (actual time=0.086..38622.215 rows=10114306 loops=3)
Doing regex count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3091955.34 rows=897 width=0) (actual time=16856.679..41398.062 rows=2 loops=3)
Doing plain count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060374.07 rows=12632507 width=0) (actual time=0.162..39454.499 rows=10114306 loops=3)
Doing plain count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060374.07 rows=12632507 width=0) (actual time=0.036..39213.171 rows=10114306 loops=3)
Doing regex count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3091955.34 rows=897 width=0) (actual time=12711.308..40015.734 rows=2 loops=3)
Doing plain count
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060374.07 rows=12632507 width=0) (actual time=0.244..39277.683 rows=10114306 loops=3)
Doing regex count
^CCancel request sent

Então, algumas perguntas:

1. O que entra nesse componente de inicialização do "tempo real" na verificação de regex e por que ele é muito maior? (10-20s vs 0-1s)

2. Embora "custo" e "tempo" não sejam unidades comparáveis, o planejador parece pensar que o custo inicial deve ser 0 em todos os casos - está sendo enganado?

3. Por que as estratégias parecem diferentes? Ambos os planos mencionam Partial Aggregate, mas a consulta regex diz que as linhas reais são 2, mas a versão simples diz que as linhas reais são ~ 10 milhões (acho que isso é algum tipo de média entre 2 trabalhadores e 1 líder, totalizando ~ 30 milhões). Se eu tivesse que implementar isso sozinho, provavelmente somaria os resultados de várias count(*)operações, em vez de mesclar linhas e contar - os planos indicam exatamente como está fazendo isso?

Então não escondo nada, abaixo estão as versões completas do plano de consulta para cada um:

EXPLAIN ANALYZE SELECT count(*) FROM ui_events_v2
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=3093171.59..3093171.60 rows=1 width=8) (actual time=39156.499..39156.499 rows=1 loops=1)
   ->  Gather  (cost=3093171.37..3093171.58 rows=2 width=8) (actual time=39156.356..39157.850 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=3092171.37..3092171.38 rows=1 width=8) (actual time=39154.405..39154.406 rows=1 loops=3)
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3060587.90 rows=12633390 width=0) (actual time=0.033..38413.690 rows=10115030 loops=3)
 Planning Time: 7.968 ms
 Execution Time: 39157.942 ms
(8 rows)


EXPLAIN ANALYZE SELECT count(*) FROM ui_events_v2 WHERE page ~ 'foo'
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=3093173.83..3093173.84 rows=1 width=8) (actual time=39908.495..39908.495 rows=1 loops=1)
   ->  Gather  (cost=3093173.61..3093173.82 rows=2 width=8) (actual time=39908.408..39909.848 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=3092173.61..3092173.62 rows=1 width=8) (actual time=39906.317..39906.318 rows=1 loops=3)
               ->  Parallel Seq Scan on ui_events_v2  (cost=0.00..3092171.37 rows=897 width=0) (actual time=17250.058..39906.308 rows=2 loops=3)
                     Filter: (page ~ 'foo'::text)
                     Rows Removed by Filter: 10115028
 Planning Time: 0.803 ms
 Execution Time: 39909.921 ms
(10 rows)

Estou tendo problemas para entender isso EXPLAINno Postgres 12.3:

EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT count(1) FROM mytable WHERE page ~ 'foo';

Esta é uma tabela de 22 GB com 30 milhões de linhas, em um servidor com 16 GB de memória. A consulta conta 7 linhas correspondentes.

Eu interpreto a saída como dizendo que 164 segundos foram gastos em E/S, mas toda a consulta levou apenas 65 segundos. Eu pensei que poderia ser uma contagem dupla de alguns trabalhadores paralelos, mas quando eu adiciono VERBOSE, também não parece somar.

Parece que está dizendo que cada um dos 2 trabalhadores passou ~ 55 segundos lendo. Se isso somar 110 segundos, como chego a 164 segundos de E/S? (Como essa consulta leva ~ 10 segundos quando as páginas são armazenadas em cache, acho que a leitura do tempo real não está muito longe de 50 segundos aqui, FWIW)

Também estou confuso como Parallel Seq Scanparece levar 32 segundos, mas ainda faltam mais 30 segundos para obter o resultado final. Eu acho que, por causa das 7 linhas que ele encontra, quase não há trabalho a fazer além da varredura. Estou lendo esta seção errado?

                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=3092377.14..3092377.15 rows=1 width=8) (actual time=65028.818..65028.818 rows=1 loops=1)
   Output: count(1)
   Buffers: shared hit=75086 read=2858433 dirtied=1
   I/O Timings: read=164712.060
   ->  Gather  (cost=3092376.92..3092377.13 rows=2 width=8) (actual time=65028.732..65030.093 rows=3 loops=1)
         Output: (PARTIAL count(1))
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=75086 read=2858433 dirtied=1
         I/O Timings: read=164712.060
         ->  Partial Aggregate  (cost=3091376.92..3091376.93 rows=1 width=8) (actual time=65026.990..65026.990 rows=1 loops=3)
               Output: PARTIAL count(1)
               Buffers: shared hit=75086 read=2858433 dirtied=1
               I/O Timings: read=164712.060
               Worker 0: actual time=65026.164..65026.164 rows=1 loops=1
                 Buffers: shared hit=25002 read=952587
                 I/O Timings: read=54906.994
               Worker 1: actual time=65026.264..65026.264 rows=1 loops=1
                 Buffers: shared hit=25062 read=954370 dirtied=1
                 I/O Timings: read=54889.244
               ->  Parallel Seq Scan on public.ui_events_v2  (cost=0.00..3091374.68 rows=896 width=0) (actual time=31764.552..65026.980 rows=2 loops=3)
                     Filter: (ui_events_v2.page ~ 'foo'::text)
                     Rows Removed by Filter: 10112272
                     Buffers: shared hit=75086 read=2858433 dirtied=1
                     I/O Timings: read=164712.060
                     Worker 0: actual time=16869.988..65026.156 rows=2 loops=1
                       Buffers: shared hit=25002 read=952587
                       I/O Timings: read=54906.994
                     Worker 1: actual time=64091.539..65026.258 rows=1 loops=1
                       Buffers: shared hit=25062 read=954370 dirtied=1
                       I/O Timings: read=54889.244
 Planning Time: 0.333 ms
 Execution Time: 65030.133 ms