All perguntas(dba)

Suponha que você tenha uma função com valor de tabela com múltiplas instruções que faça uso intenso de variáveis ​​de tabela. Que métodos existem para otimizá-los? Só sei o seguinte e todos me decepcionam.

1. Converta as variáveis ​​de tabela em variáveis ​​de tabela com otimização de memória e reze para que isso faça uma grande diferença. Dado que eles não têm estatísticas, provavelmente não terá.
2. Jogue OPTION(RECOMPILE)em todos os lugares e espere pelo melhor. Isso lhe dará melhores estimativas de cardinalidade, mas essas não são soluções mágicas.
3. Truque: Remova as variáveis ​​da tabela, converta a função em uma função embutida com valor de tabela e espere pelo melhor.
4. Truque: Converta as variáveis ​​da tabela em tabelas temporárias, converta a função em um procedimento armazenado e reze para que a mudança de interface não seja um problema.

Suponha que o SQL Server 2019, pois acho que foi a última vez que o desempenho de qualquer coisa mencionada nesta questão melhorou.

Consulta:

with firstData as
(select rm.display_name as register, br.createdat, br.status, br.id as runid
from bad_run br 
inner join bad_master bm on bm.id = br.badmasterid
inner join regis_master rm on rm.id = bm.registerid
where rm.display_name in ('abc', 'pqr')
order by br.createdat desc limit 100)

select fi.runid,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action = 'Add'  and type = 'BANK') as add,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action = 'Modify' and type = 'BANK') as modify,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action = 'Delete' and type = 'BANK') as delete,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action is null and type = 'BANK') as pending,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND (status = 'FAILED' or status = 'Failed') and type = 'BANK') as fail
from firstData fi 

Detalhes do índice:

 INDEX composite_1_idx ON public.bigTable USING btree (runid, action, type) WHERE (runid IS NOT NULL)

 INDEX composite_2_idx ON public.bigTable USING btree (runid, status, type) WHERE (runid IS NOT NULL)

 INDEX runid_idx ON public.bigTable USING btree (runid) WHERE (runid IS NOT NULL)

Detalhes da tabela:

TABLE bigTable (
    id SERIAL PRIMARY KEY,
    createdAt TIMESTAMPTZ,
    registerId BIGINT REFERENCES bank(id),
    status TEXT,
    runid BIGINT REFERENCES customer(id)
)

Problema:

It supposed to use index only scan for all scenario (Pending, Add, failed) 

It is only using index only scan for (delete, modify)   

portion of Query:
select fi.runid,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action = 'Add'  and type = 'BANK') as add,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action = 'Modify' and type = 'BANK') as modify,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action = 'Delete' and type = 'BANK') as delete,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND action is null and type = 'BANK') as pending,
            (SELECT count(*) FROM bigTable b 
            WHERE b.runid=fi.runid AND (status = 'FAILED' or status = 'Failed') and type = 'BANK') as fail
from firstData fi 
    

Plano de execução:

Subquery Scan on fi  (cost=79.27..21812.52 rows=100 width=44) (actual time=3.117..111.762 rows=100 loops=1)
  ->  Limit  (cost=79.27..79.52 rows=100 width=76) (actual time=2.119..2.194 rows=100 loops=1)
        ->  Sort  (cost=79.27..79.90 rows=250 width=76) (actual time=2.118..2.170 rows=100 loops=1)
              Sort Key: br.createdat DESC
              Sort Method: top-N heapsort  Memory: 30kB
              ->  Hash Join  (cost=2.84..69.72 rows=250 width=76) (actual time=0.093..1.630 rows=2500 loops=1)
                    Hash Cond: (br.badmasterid = bm.id)
                    ->  Seq Scan on bad_run br  (cost=0.00..55.00 rows=2500 width=20) (actual time=0.017..0.253 rows=2500 loops=1)
                    ->  Hash  (cost=2.79..2.79 rows=4 width=4) (actual time=0.063..0.065 rows=40 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 10kB
                          ->  Hash Join  (cost=1.27..2.79 rows=4 width=4) (actual time=0.045..0.058 rows=40 loops=1)
                                Hash Cond: (bm.registerid = rm.id)
                                ->  Seq Scan on bad_master bm  (cost=0.00..1.40 rows=40 width=12) (actual time=0.009..0.012 rows=40 loops=1)
                                ->  Hash  (cost=1.25..1.25 rows=2 width=4) (actual time=0.023..0.024 rows=20 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                      ->  Seq Scan on regis_master rm  (cost=0.00..1.25 rows=2 width=4) (actual time=0.011..0.016 rows=20 loops=1)
                                            Filter: (display_name = ANY ('{abc,pqr}'::text[]))
  SubPlan 1
    ->  Aggregate  (cost=11549..11549.60 rows=1 width=8) (actual time=38.433..38.43 rows=1 loops=100)
          ->  Index Scan using runid_idx on bigTable b  (cost=0.56..11475.63 rows=29585 width=0) (actual time=0.008..24.212 rows=17888 loops=100)
                Index Cond: ((runid = fi.runid))
                Filter ((type='BANK'::text) AND (action'Add'::text))
                Rows Removed by Filter: 17428
  SubPlan 2
    ->  Aggregate  (cost=19.86..19.87 rows=1 width=8) (actual time=0.120..0.120 rows=1 loops=100)
          ->  Index Only Scan using composite_1_idx on bigTable b_1  (cost=0.56..18.33 rows=612 width=0) (actual time=0.012..0.081 rows=598 loops=100)
                Index Cond: ((runid = fi.runid) AND (action = 'Modify'::text) AND (type = 'BANK'::text))
                Heap Fetches: 0
  SubPlan 3
    ->  Aggregate  (cost=19.61..19.62 rows=1 width=8) (actual time=0.122..0.122 rows=1 loops=100)
          ->  Index Only Scan using composite_1_idx on bigTable b_2  (cost=0.56..18.11 rows=602 width=0) (actual time=0.011..0.082 rows=603 loops=100)
                Index Cond: ((runid = fi.runid) AND (action = 'Delete'::text) AND (type = 'BANK'::text))
                Heap Fetches: 0
  SubPlan 4
    ->  Aggregate  (cost=11549..11549.60 rows=1 width=8) (actual time=38.433..38.43 rows=1 loops=100)
          ->  Index Scan using runid_idx on bigTable b  (cost=0.56..11475.63 rows=29585 width=0) (actual time=0.008..24.212 rows=17888 loops=100)
                  Index Cond: ((runid = fi.runid))
                  Filter ((action is null)AND (type='BANK'::text) )
                  Rows Removed by Filter: 1
  SubPlan 5
    ->  Aggregate  (cost=11549..11549.60 rows=1 width=8) (actual time=38.433..38.43 rows=1 loops=100)
          ->  Index Scan using runid_idx on bigTable b_4  (cost=0.56..11475.63 rows=29585 width=0) (actual time=0.008..24.212 rows=17888 loops=100)
                Index Cond: ((runid = fi.runid))
                Filter: (type='BANK'::text) AND ((status = 'FAILED'::text) OR (status = 'Failed'::text)))
                Rows Removed by Filter: 12730
Planning Time: 2.223 ms
Execution Time: 12282.836 ms              

Também preciso gerar números inteiros crescentes e decrescentes monotonicamente de um intervalo definido pelo usuário, sem duplicar valores nos limites do intervalo em uma coluna da tabela.

O resultado desejado para k intervalos de 10 dígitos, exceto o primeiro intervalo, é mostrado na figura abaixo na linha condicional superior.

Figura

A dificuldade que encontrei é que o primeiro intervalo consiste em n+1 dígitos, então a situação é ordenada por n dígitos.

Alguém tem alguma ideia de como resolver esse problema.

Qual é a diferença entre Effective_cache_size e shared_buffers? Fiquei confuso. Enquanto tentamos consultar uma tabela (como select), o que acontece com a consulta? qual o papel do Effective_cache_size e do shared_buffers?

Consulta:

with firstData as (
   select r.display_name as counter,
          r.createdat,
          r.status,
          r.id as runid
   from run r
      inner join customer c on c.id = r.badmasterid
      inner join bank b on b.id = c.registerid
   where b.displayname in ('abc', 'pqr')
   order by r.createdat desc limit 100
),
secondData as (
   select fi.runid,
          count(1) filter (where action = 'Add' and type = 'Bank') as addcount,
          count(1) filter (where action = 'Modify' and type = 'Bank') as modifycount,
          count(1) filter (where action = 'Delete' and type = 'Bank') as deletecount,
          count(1) filter (where action is null and type = 'Bank') as pendingcount,
          count(1) filter (where (status = 'FAILED' or status = 'FAILED') and type = 'Bank') as failcount
   from bigTable fi
   where fi.runid in
      (select runid from firstData limit 100)
   group by fi.runid
)
select r1.*,
       case when c1.add_count is null then 0 else c1.add_count end,
       case when c1.modify_count is null then 0 else c1.modify_count end,
       case when c1.delete_count is null then 0 else c1.delete_count end,
       case when c1.pending_count is null then 0 else c1.pending_count end,
       case when c1.fail_count is null then 0 else c1.fail_count end
from firstData r1
   left join secondData c1 on r1.runid = c1.runid
order by r1.createdat;

**Indexs on Table:**
run : id column
customer : id column
bank : id column
bigTable : (id, status, type, runid) columns

Plano de execução:

Sort  (cost=839936.19..839939.32 rows=1250 width=116) (actual time=7838.265..7838.275 rows=100 loops=1)
  Sort Key: r1.createdat
  Sort Method: quicksort  Memory: 34kB
  CTE result
    ->  Limit  (cost=79.27..79.52 rows=100 width=52) (actual time=1.601..1.615 rows=100 loops=1)
          ->  Sort  (cost=79.27..79.90 rows=250 width=52) (actual time=1.600..1.607 rows=100 loops=1)
                Sort Key: br.createdat DESC
                Sort Method: top-N heapsort  Memory: 35kB
                ->  Hash Join  (cost=2.84..69.72 rows=250 width=52) (actual time=0.101..1.083 rows=2500 loops=1)
                      Hash Cond: (r.runid = b.id)
                      ->  Seq Scan on run r  (cost=0.00..55.00 rows=2500 width=28) (actual time=0.029..0.213 rows=2500 loops=1)
                      ->  Hash  (cost=2.79..2.79 rows=4 width=36) (actual time=0.066..0.068 rows=40 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 10kB
                            ->  Hash Join  (cost=1.27..2.79 rows=4 width=36) (actual time=0.046..0.061 rows=40 loops=1)
                                  Hash Cond: (b.counterid = rm.id)
                                  ->  Seq Scan on bank b  (cost=0.00..1.40 rows=40 width=12) (actual time=0.011..0.014 rows=40 loops=1)
                                  ->  Hash  (cost=1.25..1.25 rows=2 width=36) (actual time=0.026..0.027 rows=20 loops=1)
                                        Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                        ->  Seq Scan on customer c  (cost=0.00..1.25 rows=2 width=36) (actual time=0.012..0.018 rows=20 loops=1)
                                              Filter: (display_name = ANY ('{abc,pqr}'::text[]))
  ->  Hash Right Join  (cost=839720.50..839792.38 rows=1250 width=116) (actual time=7838.151..7838.230 rows=100 loops=1)
        Hash Cond: (fi.runid = r1.runid)
        ->  HashAggregate  (cost=839717.25..839742.25 rows=2500 width=48) (actual time=7836.481..7836.521 rows=100 loops=1)
              Group Key: fi.runid
              Batches: 1  Memory Usage: 145kB
              ->  Hash Semi Join  (cost=3.25..810917.25 rows=720000 width=31) (actual time=615.639..7395.089 rows=719888 loops=1)
                    Hash Cond: (fi.runid = firstData.runid)
                    ->  Seq Scan on bigTable fi  (cost=0.00..755654.00 rows=18000000 width=31) (actual time=20.792..5093.711 rows=18000000 loops=1)
                    ->  Hash  (cost=2.00..2.00 rows=100 width=4) (actual time=0.046..0.047 rows=100 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 12kB
                          ->  Limit  (cost=0.00..2.00 rows=100 width=4) (actual time=0.004..0.023 rows=100 loops=1)
                                ->  CTE Scan on firstData   (cost=0.00..2.00 rows=100 width=4) (actual time=0.003..0.017 rows=100 loops=1)
        ->  Hash  (cost=2.00..2.00 rows=100 width=76) (actual time=1.660..1.661 rows=100 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 15kB
              ->  CTE Scan on firstData r1  (cost=0.00..2.00 rows=100 width=76) (actual time=1.603..1.630 rows=100 loops=1)

Planning Time: 4.387 ms
Execution Time: 7838.458 ms

Tenho algumas colunas de auditoria em minha tabela:

• modificado por
• data modificada

Eu tenho um gatilho AFTER configurado para atualizar automaticamente essas colunas sempre que os dados são inseridos ou modificados, com current_user e current_timestamp respectivamente.

Haveria algum benefício em adicioná-los também como valores de coluna padrão (presumo que não, dado que as inserções já são tratadas pelo gatilho)?

Comecei a escrever este post como uma dúvida mas, ao reproduzir o erro, encontrei a solução na documentação do PostgreSQL. Então vou compartilhar a solução aqui para a comunidade.

Uma tabela PostgreSQL 14 teve alguns dados excluídos acidentalmente.

    -- Creating table
    CREATE TABLE my_table (
        id            integer      not null  primary key  generated always as identity,
        description   varchar(50)  not null
    );

    -- Filling data
    INSERT INTO my_table (description)
    VALUES
    ('Option A'), ('Option B'), ('Option C'), ('Option D'), ('Option E'),
    ('Option F'), ('Option G'), ('Option H'), ('Option I'), ('Option J');

    SELECT * FROM my_table;

id  | description
----+------------
1   | Option A
2   | Option B
3   | Option C
4   | Option D
5   | Option E
6   | Option F
7   | Option G
8   | Option H
9   | Option I
10  | Option J

Exclusão acidental:

    DELETE FROM my_table WHERE id IN (6,7,8);

    SELECT * FROM my_table;

id  | description
----+------------
1   | Option A
2   | Option B
3   | Option C
4   | Option D
5   | Option E
9   | Option I
10  | Option J

Para reinserir essas linhas na tabela, tentei executar a seguinte instrução, definindo explicitamente valores para a coluna "id", mas gerou uma exceção:

    INSERT INTO my_table (id, description)
    VALUES
    (6, 'Option F'), (7, 'Option G'), (8, 'Option H');

[428C9] ERROR: cannot insert a non-DEFAULT value into column "id" Detail: Column "id" is an identity column defined as GENERATED ALWAYS. Hint: Use OVERRIDING SYSTEM VALUE to override.

Preciso gerar números inteiros crescentes e decrescentes monotonicamente de um determinado intervalo, por exemplo (10) em uma coluna de uma tabela.

A saída desejada de cima para baixo deve ser semelhante a:

1,2,3,4,5,6,7,8,9,10,10,9,8,7,6,5,4,3,2,1,1,2,3,4,5,6,7,8,9,10,...

Talvez usando a função generate_series(1,100)ou similar?

Tenho uma tabela que armazena trilhas e quero adicionar tempos de setor para cada trilha. Cada pista poderá ter um número diferente de setores, a mesma pista terá sempre o mesmo número de setores e os tempos dos setores podem não existir para algumas pistas.

Ao recuperar os dados, gostaria de obter também o menor tempo de setor para cada setor para uma determinada faixa.

A melhor ideia que pude ter é ter um objeto json com sector1, sector2, ... etc. Tudo bem, mas requer que o código que recupera os dados saiba o número de setores para SELECTele, como no exemplo abaixo. Isto significa que o "número de setores" também precisaria ser armazenado.

CREATE TABLE test (track TEXT NOT NULL, sectors TEXT);
INSERT INTO test (track, sectors) VALUES ('track1', json_object('sector1', 10, 'sector2', 11, 'sector3', 12));
INSERT INTO test (track, sectors) VALUES ('track1', json_object('sector1', 11, 'sector2', 9, 'sector3', 12));
INSERT INTO test (track, sectors) VALUES ('track1', json_object('sector1', 10, 'sector2', 9, 'sector3', 12));
INSERT INTO test (track, sectors) VALUES ('track1', json_object('sector1', 12, 'sector2', 12, 'sector3', 4));
INSERT INTO test (track, sectors) VALUES ('track2', json_object('sector1', 12, 'sector2', 12, 'sector3', 3, 'sector4', 1));
INSERT INTO test (track) VALUES ('track1');
select * from test;
select min(DISTINCT json_extract(sectors, '$.sector1')), min(DISTINCT json_extract(sectors, '$.sector2')), min(DISTINCT json_extract(sectors, '$.sector3')) from test where track = 'track1';

Existe talvez uma maneira melhor de armazenar dados como este e poder fazê- SELECTlo como afirmei acima?

Há um trabalho em lote Spring com uma exclusão e muitas inserções em uma transação com nível de isolamento READ_COMMITTED:

delete from TABLE_A;
insert into TABLE_A values (...);
insert into TABLE_A values (...);
...

No entanto, quando tentei selecionar no banco de dados TABLE_A enquanto o trabalho em lote ainda estava em execução (ainda não confirmado), a seleção não foi bloqueada pelo bloqueio exclusivo que eu esperava porque houve uma exclusão completa da tabela na transação. Posso saber se o MariaDB possui algum mecanismo de bloqueio especial?