Vadim Samokhin's questions

Tenho a seguinte consulta:

select ro.*
from courier c1
    join courier c2 on c2.real_physical_courier_1c_id = c1.real_physical_courier_1c_id
    join restaurant_order ro on ro.courier_id = c2.id
    left join jsonb_array_elements(items) jae on true
    left join jsonb_array_elements(jae->'options') ji on true
    inner join catalogue c on c.id in ((jae->'id')::int, (ji->'id')::int)
    join restaurant r on r.id = ro.restaurant_id
where c1.id = '7b35cdab-b423-472a-bde1-d6699f6cefd3' and ro.status in (70, 73)
group by ro.order_id, r.id ;

Aqui está uma parte de um plano de consulta que leva cerca de 95% do tempo:

->  Parallel Bitmap Heap Scan on restaurant_order ro  (cost=23.87..2357.58 rows=1244 width=1257) (actual time=11.931..38.163 rows=98 loops=2)"
      Recheck Cond: (status = ANY ('{70,73}'::integer[]))"
      Heap Blocks: exact=28755"
      ->  Bitmap Index Scan on ro__status  (cost=0.00..23.34 rows=2115 width=0) (actual time=9.168..9.168 rows=51540 loops=1)"
            Index Cond: (status = ANY ('{70,73}'::integer[]))"

Eu tenho algumas perguntas.

1. Primeiro, uma seção de verificação de índice de bitmap. O Postgres percorre 51.540 registros de índice ro__status com base Index Cond: (status = ANY ('{70,73}'::integer[]))"e cria um bitmap com 28.755 elementos. Suas chaves são a localização física das linhas da tabela correspondentes (indicadas exactna Heap Blocksseção). Isso está correto?
2. Segundo, este mapa é passado para a fase Bitmap Heap Scan. Recheck Condnão é realmente executado, pois os blocos de heap não são do estilo com perdas. Bitmap Heap Scan classifica um bitmap pela localização física das tuplas para permitir um acesso sequencial. Em seguida, ele lê os dados da tabela sequencialmente em duas etapas ( loops=2) e não obtém mais de 196 linhas da tabela. Isso é correto?
3. O tamanho do bitmap refletido na Heap Blocks: exact=28755linha varia muito ao longo do tempo. A diferença é de duas ordens de grandeza. Por exemplo, ontem eram cerca de 500. Por que isso acontece?
4. Agora, por que um bitmap criado durante a fase de verificação de índice de bitmap tem tantas chaves? Existe um índice ro__status que pode indicar que existem apenas cerca de 200 registros com status 70 e 73. Não consigo pensar em nenhuma razão que impeça o postgres de manter apenas as chaves que realmente satisfazem um index cond. A sobrecarga parece ser enorme: em vez de ~ 200 chaves, existem 28755!
5. Por que o Bitmap Heap Scan demora tanto? Pelo que vi são duas leituras sequenciais ( loops=2), deve demorar bem menos, não? Ou, a classificação de bitmap pela localização física das tuplas é o principal culpado?
6. Devo me preocupar com estimativas ruins? Se sim, aumentar default_statistics_target deve ajudar, certo? Agora é o padrão 100.

Apenas no caso, aqui está um plano completo:

"Group  (cost=51297.15..52767.65 rows=19998 width=1261) (actual time=42.555..42.555 rows=0 loops=1)"
"  Group Key: ro.order_id, r.id"
"  ->  Gather Merge  (cost=51297.15..52708.83 rows=11764 width=1261) (actual time=42.554..45.459 rows=0 loops=1)"
"        Workers Planned: 1"
"        Workers Launched: 1"
"        ->  Group  (cost=50297.14..50385.37 rows=11764 width=1261) (actual time=38.850..38.850 rows=0 loops=2)"
"              Group Key: ro.order_id, r.id"
"              ->  Sort  (cost=50297.14..50326.55 rows=11764 width=1261) (actual time=38.850..38.850 rows=0 loops=2)"
"                    Sort Key: ro.order_id, r.id"
"                    Sort Method: quicksort  Memory: 25kB"
"                    Worker 0:  Sort Method: quicksort  Memory: 25kB"
"                    ->  Nested Loop  (cost=31.84..45709.27 rows=11764 width=1261) (actual time=38.819..38.819 rows=0 loops=2)"
"                          ->  Nested Loop Left Join  (cost=27.21..5194.50 rows=5882 width=1325) (actual time=38.819..38.819 rows=0 loops=2)"
"                                ->  Nested Loop Left Join  (cost=27.20..5076.49 rows=59 width=1293) (actual time=38.818..38.818 rows=0 loops=2)"
"                                      ->  Nested Loop  (cost=27.20..5074.49 rows=1 width=1261) (actual time=38.818..38.818 rows=0 loops=2)"
"                                            ->  Hash Join  (cost=26.93..5073.59 rows=1 width=1257) (actual time=38.817..38.818 rows=0 loops=2)"
"                                                  Hash Cond: (c2.real_physical_courier_1c_id = c1.real_physical_courier_1c_id)"
"                                                  ->  Nested Loop  (cost=24.28..5068.22 rows=1038 width=1267) (actual time=11.960..38.732 rows=98 loops=2)"
"                                                        ->  Parallel Bitmap Heap Scan on restaurant_order ro  (cost=23.87..2357.58 rows=1244 width=1257) (actual time=11.931..38.163 rows=98 loops=2)"
"                                                              Recheck Cond: (status = ANY ('{70,73}'::integer[]))"
"                                                              Heap Blocks: exact=28755"
"                                                              ->  Bitmap Index Scan on ro__status  (cost=0.00..23.34 rows=2115 width=0) (actual time=9.168..9.168 rows=51540 loops=1)"
"                                                                    Index Cond: (status = ANY ('{70,73}'::integer[]))"
"                                                        ->  Index Scan using courier_pkey on courier c2  (cost=0.41..2.18 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=195)"
"                                                              Index Cond: (id = ro.courier_id)"
"                                                  ->  Hash  (cost=2.63..2.63 rows=1 width=10) (actual time=0.039..0.039 rows=1 loops=2)"
"                                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"                                                        ->  Index Scan using courier_pkey on courier c1  (cost=0.41..2.63 rows=1 width=10) (actual time=0.034..0.034 rows=1 loops=2)"
"                                                              Index Cond: (id = '7b35cdab-b423-472a-bde1-d6699f6cefd3'::uuid)"
"                                            ->  Index Only Scan using restaurant_pkey on restaurant r  (cost=0.27..0.89 rows=1 width=4) (never executed)"
"                                                  Index Cond: (id = ro.restaurant_id)"
"                                                  Heap Fetches: 0"
"                                      ->  Function Scan on jsonb_array_elements jae  (cost=0.00..1.00 rows=100 width=32) (never executed)"
"                                ->  Function Scan on jsonb_array_elements ji  (cost=0.01..1.00 rows=100 width=32) (never executed)"
"                          ->  Bitmap Heap Scan on catalogue c  (cost=4.63..6.87 rows=2 width=4) (never executed)"
"                                Recheck Cond: ((id = ((jae.value -> 'id'::text))::integer) OR (id = ((ji.value -> 'id'::text))::integer))"
"                                ->  BitmapOr  (cost=4.63..4.63 rows=2 width=0) (never executed)"
"                                      ->  Bitmap Index Scan on catalogue_pkey  (cost=0.00..0.97 rows=1 width=0) (never executed)"
"                                            Index Cond: (id = ((jae.value -> 'id'::text))::integer)"
"                                      ->  Bitmap Index Scan on catalogue_pkey  (cost=0.00..0.97 rows=1 width=0) (never executed)"
"                                            Index Cond: (id = ((ji.value -> 'id'::text))::integer)"
"Planning Time: 1.113 ms"
"Execution Time: 45.588 ms"

Eu executei uma consulta de migração dentro de uma transação , ficou assim:

alter table big_and_loaded_table
    add column col1 bool;

update big_and_loaded_table set col1 = false;

Parece bastante inocente, mas levou cerca de dois minutos para ser executado. Além disso, ele "bloqueou" big_and_loaded_table: qualquer consulta no meu aplicativo que o envolvesse - leitura e gravação - demorava tanto para ser executada, o mesmo minuto ou mais. Eu não uso transações em minha aplicação, assim como qualquer tipo de bloqueio explícito.

Então eu tenho duas perguntas:

1. Por que essa consulta de migração demorou tanto para ser executada? Foi por causa de uma transação? Ou foi por causa da própria consulta?
2. Por que bloqueou as consultas de aplicativos que envolviam a tabela big_and_loaded_table?
3. Como devo executar esses tipos de migrações no futuro? Provavelmente este ponto não fará nenhum sentido quando eu descobrir os dois primeiros.

A versão do Postgres é 11.6.

Eu tenho um script sql que recria meu banco de dados de teste de unidade. No momento é executado com \i <path_to_my_script>. Vai como

// create user, give him some priveleges

CREATE DATABASE my_database;

CREATE TABLE my_table (
  id UUID primary key,
  data json
);

Mas my_tableé criado no postgresbanco de dados, não no my_database.

Como posso especificar que my_tabledeve ser criado em my_database?

Eu uso o postgresql 10.5.

Citação de documentos:

Read Committed é o nível de isolamento padrão no PostgreSQL. Quando uma transação usa esse nível de isolamento, uma consulta SELECT (sem uma cláusula FOR UPDATE/SHARE) vê apenas os dados confirmados antes do início da consulta; ele nunca vê dados não confirmados ou alterações confirmadas durante a execução da consulta por transações simultâneas . Na verdade, uma consulta SELECT vê um instantâneo do banco de dados no instante em que a consulta começa a ser executada. No entanto, SELECT vê os efeitos de atualizações anteriores executadas em sua própria transação, mesmo que ainda não tenham sido confirmadas. Observe também que dois comandos SELECT sucessivos podem ver dados diferentes, mesmo que estejam dentro de uma única transação, se outras transações cometerem alterações durante a execução do primeiro SELECT .

Então, o PostgreSQL vê as alterações confirmadas por outras transações ou não?

Eu uso o PostgreSQL 9.2 e não uso bloqueio explícito em nenhum lugar, nem LOCKinstrução nem SELECT ... FOR UPDATE. No entanto, recentemente eu tenho ERROR: 40P01: deadlock detected. A consulta onde o impasse foi detectado é agrupada no bloco de transação. De qualquer forma, como assim?

A questão é sobre consultas que não são agrupadas no bloco 'begin-commit', mas sobre inserções e atualizações simples que são atômicas no PostgreSQL, MySQL (mecanismo innodb, pelo menos). Então, como isso é implementado internamente?

VACUUMgeralmente não retorna espaço em disco para o sistema operacional, exceto em alguns casos especiais.
Dos documentos:

A forma padrão de VACUUMremove versões de linhas inativas em tabelas e índices e marca o espaço disponível para reutilização futura. No entanto, ele não retornará o espaço para o sistema operacional, exceto no caso especial em que uma ou mais páginas no final de uma tabela ficam totalmente livres e um bloqueio de tabela exclusivo pode ser facilmente obtido. Em contraste, VACUUM FULLativamente compacta as tabelas escrevendo uma nova versão completa do arquivo de tabela sem espaço morto. Isso minimiza o tamanho da tabela, mas pode levar muito tempo. Também requer espaço em disco extra para a nova cópia da tabela, até que a operação seja concluída.

A questão é: como esse estado de banco de dados pode one or more pages at the end of a table become entirely freeser alcançado? Isso pode ser feito via VACUUM FULL, mas não tenho espaço suficiente para implementá-lo. Então, existem outras possibilidades?