关于【query-performance】的问题- 第1页

gherkins

Asked: 2025-03-06 14:45:28 +0800 CST

Recomendação de banco de dados/índice de pesquisa: combinar intervalos de tempo de diferentes categorias

5

Mais de 300 mil vídeos
Mais de 10 milhões de marcadores apontando para intervalos de tempo em vídeos

{
  "markerCategory": "something",
  "markerDesc": "something-more-specific",
  "frameIn": 120001,
  "frameOut": 140002
},
{
  "markerCategory": "something-else",
  "markerDesc": "something-else-more-specific",
  "frameIn": 130001,
  "frameOut": 135002
}

Alguma sugestão de qual banco de dados/índice de pesquisa teria melhor desempenho
ao pesquisar algo neste sentido:

Vídeos com eventos de category A AND category B em intervalos de tempo sobrepostos,
classificados por quantidade de tempo coberto

Atualmente, os vídeos são exportados de algum banco de dados relacional proprietário e armazenados em uma instância do Apache SOLR para pesquisa.

Existe um nome específico para esse tipo de consulta ("consultas de intervalo invertido" ou algo assim...)?
Alguma sugestão de qual tecnologia teria melhor desempenho para esses tipos de consultas?
Eu estava pensando talvez em Elasticsearch?

ToC

Asked: 2024-11-20 03:53:40 +0800 CST

Sql Server: Consulta para analisar e validar códigos

6

Temos uma tabela #ValidCode com uma lista de códigos válidos como: 'A', 'B', 'C', etc. Outra tabela chamada #SourceData com dados de entrada — que vêm como uma combinação de tokens válidos e inválidos (às vezes duplicados).

Ex:

'A;B;C' ( válido )
'A;A;A;A;A;B' ( Válido )
'ad;df;A;B' ( inválido )

Tentando encontrar uma abordagem de consulta ótima para processar essas strings para encontrar linhas válidas em #SourceData . Veja o exemplo abaixo:

DROP TABLE IF EXISTS #ValidCode
GO
CREATE TABLE #ValidCode
(
      ID        INT             IDENTITY(1,1)
    , Code      CHAR(1)
)
INSERT INTO #ValidCode (Code) VALUES ('A'), ('B'), ('C')
GO
DROP TABLE IF EXISTS #SourceData 
GO
CREATE TABLE #SourceData 
(
      ID        INT             IDENTITY(1,1)
    , Codes     VARCHAR(500)
    , Is_Valid  BIT
    , Is_Split  BIT
)

INSERT INTO #SourceData (Codes) 
VALUES    ('A;B;C')
        , ('B;A')
        , ('B;B;B;C;C;A;A;B')
        , ('B;Z;1')
        , ('B;ss;asd')


SELECT * FROM #ValidCode
SELECT * FROM #SourceData

A consulta processaria os dados na tabela #SourceData e atualizaria o sinalizador Is_Valid para que eles pudessem ser consumidos no processo subsequente.

Regras :

Cada token deve ser válido para que toda a linha da coluna seja válida (linhas 1 a 3)
Mesmo que um token seja inválido, o valor da linha inteira será inválido (linhas 4 e 5)

Então, esta é a saída preferida:

EU IA	Códigos	É_válido
1	ABC	1
2	B;Um	1
3	B;B;B;C;C;A;A;B	1
4	B;Z;1	0
5	B;ss;asd	0

Abordagem atual : Faça um loop por cada linha em #SourceData e divida-as no delimitador ';', depois compare-as com a tabela #ValidCode . Se todos os tokens forem individualmente válidos, marque a linha em #SourceData como válida ( flag Is_Valid ). Caso contrário, marque como inválida. A WHILEabordagem de loop funciona, mas é lenta.

O #SourceData pode ter até 3 milhões de linhas. Com cada linha tendo várias combinações de valores válidos duplicados ('A;A;A;A') e inválidos ('A;as;sdf;B')

Existe uma abordagem melhor?

Obrigado!

Pantea

Asked: 2024-11-11 21:10:37 +0800 CST

Consulta para excluir registros com eff_date menor em uma tabela grande com 400 milhões de registros

9

Tenho uma tabela com a seguinte estrutura:

create table TEST_TAB
(
  activity_type CHAR(1),
  tracking_code NUMBER,
  eff_date      DATE
)

Dados de amostra para esta tabela:

insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('A', 1, to_date('01-11-2020', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('A', 1, to_date('02-01-2024', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('01-08-2023', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('02-08-2023', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('03-08-2023', 'dd-mm-yyyy'));

Este é apenas um dado de amostra e a quantidade de dados reais na tabela original é de quase 400 milhões de registros. O que preciso fazer é que, para cada grupo de activity_type, tracking_code, preciso manter o registro que tem o maior "eff_date" e excluir o restante. Então, para o , activity_type=A and tracking_code = 1preciso manter um com eff_date = 1/2/2024e excluir o outro. O que tenho por enquanto é a consulta abaixo:

delete from test_tab
 where rowid in (select rid
                   from (select rowid as rid,
                                row_number() over(partition by activity_type, tracking_code order by eff_date desc) as row_num
                           from test_tab
                           )
                  where row_num > 1
                  )

No entanto, isso parece muito lento. Você poderia sugerir alguma solução melhor? A tabela original é particionada em eff_date e tem índice nas duas colunas restantes. Outro ponto é que pode haver mais de um intervalo de um ano entre eff_dates de cada registro em um único grupo.

desde já, obrigado

Sicilian-Najdorf

Asked: 2024-10-09 17:09:35 +0800 CST

Estatísticas de atualização automática - Plano de consulta ruim

7

Tenho uma consulta que atinge uma tabela de 1,4 bilhão de linhas.

Tudo funciona bem com essa consulta, em menos de um segundo.

Então, uma vez a cada 5 dias, mais ou menos, as estatísticas de atualização automática entram em ação e a consulta começa a ser executada por quase 60 segundos. Ela adota um plano diferente.

Verifiquei e quando as estatísticas de atualização automática são ativadas, a taxa de amostragem é inferior a 1% da tabela.

Agora, minha pergunta é: uma atualização automática de estatísticas pode fazer com que o otimizador escolha um plano pior do que o anterior?

Posso ver que as estimativas do plano ruim são muito diferentes das do plano bom.

Bom plano:

Plano Ruim:

As estimativas estão claramente erradas para o plano ruim e, ainda assim, a quantidade de linhas retornadas pela consulta é a mesma.

Neste ponto, estou tentando confirmar se uma atualização automática de estatísticas pode fazer isso?

O que estou tentando agora é fazer uma atualização de estatísticas com uma amostra de 10% e ver o que acontece.

Atualização: Concluí uma atualização de estatísticas com taxa de amostragem de 10% e as consultas estão indo bem novamente.

Tornei isso uma tarefa a ser executada regularmente.

Stark

Asked: 2024-10-08 00:20:06 +0800 CST

Azure SQL: problemas de desempenho do hierarchyid

11

(Desculpas pela postagem longa - primeira postagem no SE)

Tenho usado o tipo hierarchyid em vários aplicativos por cerca de 10 anos e, no geral, estou satisfeito com o desempenho. Ocasionalmente, me deparei com relatos de desempenho ruim com hierarchyid e sempre presumi que era devido a índices ou consultas configurados incorretamente que poderiam ser otimizados. Mas agora eu mesmo me deparei com o problema.

Azure SQL, nível de compatibilidade de banco de dados definido como 160 (SQL Server 2022).

Tenho a seguinte tabela:

create table [Dim]
(
    [Key] [int] not null,
    [Path] [hierarchyid] null,
    [Name] [nvarchar](256) null,
    [ParentKey] [int] null,
    [SortOrder] [int] null,
    [IsLeaf] [bit] null,
    constraint PK_Dim primary key clustered ([Key]),
    index IX_Dim_Name unique ([Name]) include ([Path]),
    index IX_Dim_ParentKey unique ([ParentKey], [SortOrder]),
    index IX_Dim_Path ([Path]),
    index IX_Dim_Leaf ([IsLeaf], [Path])
)

A tabela é projetada para modelar uma hierarquia pai-filho. O campo Path reflete a hierarquia, com base no Path pai e no SortOrder do membro atual. Há 10.010 registros no conjunto de dados de teste com uma profundidade de hierarquia máxima de 8. Executamos a seguinte consulta:

select d.[Key], r.[SortOrder], r.[Key], r.[Name], r.[Level]
from (
    select row_number() over (order by d.[Path]) as [SortOrder], d.[Key], d.[Name], d.[Path].GetLevel() as [Level], d.[Path]
    from [Dim] d
    inner join [Dim] p on (d.[Path].IsDescendantOf(p.[Path]) = 1)
    where p.[Name] = 'A8'
) r
inner join [Dim] d on (d.[Path].IsDescendantOf(r.[Path]) = 1
        and d.[IsLeaf] = 1);

A consulta leva 14 segundos para ser executada e retorna 3.053 linhas. A subconsulta retorna 1.298 linhas. Abaixo está o plano de execução: e um link: https://www.brentozar.com/pastetheplan/?id=SydK3qIk1l

que parece muito com o que eu esperava, com exceção da varredura de índice em IX_Dim_Leaf, que lê 6.861.228 linhas.

Agora, se adicionarmos uma coluna adicional à tabela que contém a representação de string do campo Caminho e os dois índices correspondentes no novo campo:

alter table [Dim] add [PathStr] varchar(256);
go
update [Dim] set [PathStr] = [Path].ToString();
create index [IX_Dim_PathStr] on [Dim] ([PathStr]);
create index [IX_Dim_LeafStr] on [Dim] ([IsLeaf], [PathStr]);
go

e então reescreva a consulta para usar PathStr em vez de Path:

select d.[Key], r.[SortOrder], r.[Key], r.[Name], r.[Level]
from (
    select row_number() over (order by d.[PathStr]) as [SortOrder], d.[Key], d.[Name], d.[Path].GetLevel() as [Level], d.[PathStr]
    from [Dim] d
    inner join [Dim] p on (d.[PathStr] like p.[PathStr] + '%')
    where p.[Name] = 'A8'
) r
inner join [Dim] d on (d.[PathStr] like r.[PathStr] + '%'
    and d.[IsLeaf] = 1);

A nova consulta é executada em 0,064 segundos. O plano de execução está abaixo: e o link: https://www.brentozar.com/pastetheplan/?id=Sy5oT5Uyye

Na verdade, é mais complexo que o primeiro plano de consulta, pois o novo campo não está incluído no índice IX_Dim_Name e é preciso adicionar a string '%', mas a grande diferença está na varredura do índice externo, onde apenas 3.053 linhas são lidas em vez de 6,8 milhões.

Não faz sentido para mim que um campo de string tenha um desempenho melhor do que um campo hierarchyid que teoricamente é otimizado para esse tipo de consulta hierárquica. Estou fazendo algo errado ou o SQL Server é simplesmente incapaz de lidar com hierarchyids em subconsultas e devemos continuar com um campo de string?

Observação: armazenar os resultados da subconsulta em uma tabela var e, em seguida, unir a tabela var com a tabela Dim tem um desempenho marginalmente melhor ao usar hierarchyid, mas infelizmente isso não é uma opção.

Edição: seguindo a sugestão do Charlieface abaixo, também tentei esta consulta:

select d.[Key], r.[SortOrder], r.[Key], r.[Name], r.[Level]
from (
    select row_number() over (order by d.[Path]) as [SortOrder], d.[Key], d.[Name], d.[Path].GetLevel() as [Level], d.[Path]
    from [Dim] d
    inner join [Dim] p on (d.[Path].IsDescendantOf(p.[Path]) = 1)
    where p.[Name] = 'A8'
) r
inner join [Dim] d on (d.[Path].GetAncestor(1) = r.[Path]
        and d.[IsLeaf] = 1);

que executa em 38 ms ( execução do plano ). Parece que apenas IsDescendantOf() é um problema.

Enzo Damato

Asked: 2024-09-10 07:46:40 +0800 CST

Em um "select * where col=x" onde x é uma coluna não ordenável e não indexada, um banco de dados oferece algum ganho de desempenho em relação a um arquivo simples?

5

Se eu estiver consultando um banco de dados para selecionar todos os registros que têm um VARCHAR()campo igual a um parâmetro de consulta. Há alguma vantagem de velocidade em fazer uma pesquisa linear linha por linha de um "banco de dados" de arquivo simples? Se sim, como essa aceleração é realizada?

Elikill58

Asked: 2024-08-28 20:08:56 +0800 CST

Como otimizar múltiplos WHEN que estão usando diferentes strings estáticas?

5

Eu tenho uma tabela assim:

Eu ia	Observação	Cliente
1	Nota longa com várias linhas Cliente: Nome	nulo
2	Outra nota que tenha várias linhas Cliente: Nome	nulo
3	Terceira nota que tem várias linhas Cliente: Linha de nome	nulo
4	Linha: A Cliente: Linha de nome	nulo
5	Linha B B: Linha Cliente: Nome linha outros	nulo

Quero extrair informações do cliente da coluna "Nota" para colocá-las na coluna "Cliente" com apenas uma consulta SQL.

Dei alguns exemplos que representam os milhares de linhas reais que tenho. Primeiro fiz esta consulta:

SELECT
    CASE
        WHEN posclient != 0 THEN SUBSTRING(
            Note,
            posclient + LENGTH('Client: '),
            LOCATE('\n', Note, posclient + LENGTH('Client: ')) - (posclient + LENGTH('Client: '))
        )
        WHEN poscustomer != 0 THEN SUBSTRING(
            Note,
            poscustomer + LENGTH('Customer : '),
            LOCATE('\n', Note, poscustomer + LENGTH('Customer : ')) - (poscustomer + LENGTH('Customer : '))
        )
        WHEN poscontact != 0 THEN SUBSTRING(
            Note,
            poscontact + LENGTH('Contact : '),
            LOCATE('\n', Note, poscontact + LENGTH('Contact : ')) - (poscontact + LENGTH('Contact : '))
        )
        ELSE ''
    END AS client,
    Id
FROM (SELECT Id, Note,
        LOCATE('Customer : ', Note) as poscustomer,
        LOCATE('Client: ', Note) as posclient,
        LOCATE('Contact : ', Note) as poscontact
    FROM myTable) x 
WHERE (poscustomer != 0 OR posclient != 0 OR poscontact != 0)

Como há código duplicado (a WHENparte), é difícil de ler e não fica muito bom se eu tiver outra maneira de adicionar.

E eu tenho: há pelo menos 6 maneiras de escrever o cliente. Quero otimizar esta parte:

WHEN poscontact != 0 THEN SUBSTRING(
   Note,
   poscontact + LENGTH('Contact : '),
   LOCATE('\n', Note, poscontact + LENGTH('Contact : ')) - (poscontact + LENGTH('Contact : '))
)

Para ser "genérico", mas não sei como, pois não estou em linguagem de programação. Em php, isso seria fácil, mas não posso usar linguagem de programação, tenho que usar apenas uma consulta.

Uma coisa que pode ser muito útil: há apenas UMA maneira de escrever o cliente para cada linha. Ele sempre conterá apenas um.

Como posso fazer?

Parker

Asked: 2024-08-08 21:50:13 +0800 CST

Qual é a causa dessa consulta lenta intermitente ao contar linhas em uma única coluna de texto indexada no PostgreSQL?

5

Tenho uma tabela com 2.395.015 linhas, onde uma TEXTcoluna tem um de três valores e nunca é NULL. Tenho problemas intermitentes de desempenho de consulta ao contar o número de linhas em que o valor corresponde à maioria (>99%) das linhas. Quero corrigir esse problema de desempenho. Essas consultas devem retornar contagens exatas, então não posso usar contagens aproximadas.

corpus=# \d metadata
                             Table "public.metadata"
    Column     |            Type             | Collation | Nullable |    Default
---------------+-----------------------------+-----------+----------+----------------
 id            | text                        |           | not null |
 priority      | integer                     |           | not null | 10
 media_type    | text                        |           | not null |
 modified      | timestamp without time zone |           | not null | now()
 processed     | timestamp without time zone |           |          |
 status        | text                        |           | not null | 'QUEUED'::text
 note          | text                        |           |          |
 content       | text                        |           |          |
 resolved      | text                        |           |          |
 response_time | integer                     |           |          |
 luid          | integer                     |           | not null |
 jamo_date     | timestamp without time zone |           |          |
 audit_path    | text                        |           |          |
Indexes:
    "metadata_pkey" PRIMARY KEY, btree (id)
    "metadata_id_idx" btree (id)
    "metadata_luid_idx" btree (luid)
    "metadata_modified_idx" btree (modified DESC)
    "metadata_processed_idx" btree (processed DESC)
    "metadata_status_idx" btree (status)
Check constraints:
    "media_type_ck" CHECK (media_type = ANY (ARRAY['text/json'::text, 'text/yaml'::text]))
    "status_ck" CHECK (status = ANY (ARRAY['QUEUED'::text, 'PROCESSED'::text, 'ERROR'::text]))
Foreign-key constraints:
    "metadata_luid_fkey" FOREIGN KEY (luid) REFERENCES concept(luid) ON DELETE CASCADE

corpus=#

Tenho algumas consultas simples que contam o número de linhas que correspondem a um dos três códigos de status ( QUEUED, PROCESSED, ERROR). Há 0 linhas que correspondem a QUEUED, 9.794 que correspondem a ERROR, e 2.385.221 que correspondem a PROCESSED. Quando executo uma consulta idêntica em cada um desses códigos de status, geralmente obtenho um conjunto de resultados prontamente:

corpus=# EXPLAIN ANALYZE VERBOSE SELECT COUNT(*) FROM metadata WHERE status='QUEUED';
                                                                          QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1947.17..1947.18 rows=1 width=8) (actual time=2.935..2.936 rows=1 loops=1)
   Output: count(*)
   ->  Index Only Scan using metadata_status_idx on public.metadata  (cost=0.43..1915.97 rows=12480 width=0) (actual time=2.932..2.933 rows=0 loops=1)
         Output: status
         Index Cond: (metadata.status = 'QUEUED'::text)
         Heap Fetches: 0
 Planning Time: 0.734 ms
 Execution Time: 2.988 ms
(8 rows)

corpus=# EXPLAIN ANALYZE VERBOSE SELECT COUNT(*) FROM metadata WHERE status='ERROR';
                                                                             QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1184.19..1184.20 rows=1 width=8) (actual time=1484.763..1484.764 rows=1 loops=1)
   Output: count(*)
   ->  Index Only Scan using metadata_status_idx on public.metadata  (cost=0.43..1165.26 rows=7569 width=0) (actual time=4.235..1484.029 rows=9794 loops=1)
         Output: status
         Index Cond: (metadata.status = 'ERROR'::text)
         Heap Fetches: 9584
 Planning Time: 0.072 ms
 Execution Time: 1484.786 ms
(8 rows)

corpus=#

corpus=# EXPLAIN ANALYZE VERBOSE SELECT COUNT(*) FROM metadata WHERE status='PROCESSED';
                                                                                          QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=261398.83..261398.84 rows=1 width=8) (actual time=741.319..749.026 rows=1 loops=1)
   Output: count(*)
   ->  Gather  (cost=261398.62..261398.83 rows=2 width=8) (actual time=741.309..749.020 rows=3 loops=1)
         Output: (PARTIAL count(*))
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=260398.62..260398.63 rows=1 width=8) (actual time=735.099..735.100 rows=1 loops=3)
               Output: PARTIAL count(*)
               Worker 0: actual time=730.871..730.872 rows=1 loops=1
               Worker 1: actual time=733.435..733.436 rows=1 loops=1
               ->  Parallel Index Only Scan using metadata_status_idx on public.metadata  (cost=0.43..257903.37 rows=998100 width=0) (actual time=0.065..700.529 rows=795074 loops=3)
                     Output: status
                     Index Cond: (metadata.status = 'PROCESSED'::text)
                     Heap Fetches: 747048
                     Worker 0: actual time=0.060..702.980 rows=670975 loops=1
                     Worker 1: actual time=0.076..686.946 rows=1010099 loops=1
 Planning Time: 0.085 ms
 Execution Time: 749.068 ms
(18 rows)

corpus=#

Mas, ocasionalmente, a contagem de PROCESSEDlinhas leva muito tempo (às vezes, vários minutos):

corpus=# EXPLAIN ANALYZE VERBOSE SELECT COUNT(*) FROM metadata WHERE status='PROCESSED';
                                                                                           QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=261398.83..261398.84 rows=1 width=8) (actual time=30019.273..30019.336 rows=1 loops=1)
   Output: count(*)
   ->  Gather  (cost=261398.62..261398.83 rows=2 width=8) (actual time=30019.261..30019.326 rows=3 loops=1)
         Output: (PARTIAL count(*))
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=260398.62..260398.63 rows=1 width=8) (actual time=29967.734..29967.735 rows=1 loops=3)
               Output: PARTIAL count(*)
               Worker 0: actual time=29939.915..29939.916 rows=1 loops=1
               Worker 1: actual time=29944.395..29944.395 rows=1 loops=1
               ->  Parallel Index Only Scan using metadata_status_idx on public.metadata  (cost=0.43..257903.37 rows=998100 width=0) (actual time=75.385..29931.795 rows=795074 loops=3)
                     Output: status
                     Index Cond: (metadata.status = 'PROCESSED'::text)
                     Heap Fetches: 747151
                     Worker 0: actual time=128.857..29899.156 rows=916461 loops=1
                     Worker 1: actual time=28.609..29905.708 rows=854439 loops=1
 Planning Time: 421.203 ms
 Execution Time: 30019.440 ms
(18 rows)

corpus=#

Enquanto a consulta acima está sendo executada lentamente, consigo consultar a mesma tabela para qualquer um dos outros dois códigos, e essas consultas retornam em 1 segundo. Procurei por bloqueios de tabela (não há nenhum). Isso acontece mesmo quando não há outras consultas ou inserções de tabela em execução.

Quais são as possíveis causas para essas consultas lentas intermitentes?
Que depuração adicional posso tentar para obter mais informações sobre essas consultas lentas?
Há alguma configuração de servidor relevante?
Existe uma maneira mais eficiente de indexar/codificar essas colunas (por exemplo, devo usar um CHAR(1)), ou mesmo um SMALLINT? Se sim, qual índice deve ser usado para a coluna?

Se eu usar um CHAR(1), há alguma diferença entre as seguintes restrições:

ALTER TABLE jgi_metadata ADD CONSTRAINT status_code_ck CHECK (status_code = ANY (ARRAY['Q'::char(1), 'P'::char(1), 'E'::char(1)]));
ALTER TABLE jgi_metadata ADD CONSTRAINT status_code_ck CHECK (status_code IN ('Q', 'P', 'E'));
Um índice parcial poderia ser usado para esta coluna, mesmo que isso nunca aconteça NULL?
Devo dividir o PROCESSEDoff em uma coluna booleana e então usar a statuscoluna somente para os outros códigos e torná-la anulável com um índice parcial?

Este é o PostgreSQL 11 com configurações padrão, rodando no Linux.

Outras coisas que tentei:

Aumentou o work_mem para 100 MB (via postgresql.conf). Nenhuma alteração no desempenho.
Tentei criar um índice parcial na coluna de status.

Atualização: Descobri que esse problema de desempenho não tem nada a ver com a coluna de status, mas sim com o tamanho da tabela em si, como mostra a consulta de 2 minutos a seguir:

corpus=# EXPLAIN ANALYZE VERBOSE SELECT COUNT(*) FROM metadata;
                                                                                            QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=196398.52..196398.53 rows=1 width=8) (actual time=118527.897..118554.762 rows=1 loops=1)
   Output: count(*)
   ->  Gather  (cost=196398.30..196398.51 rows=2 width=8) (actual time=118522.165..118554.756 rows=3 loops=1)
         Output: (PARTIAL count(*))
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=195398.30..195398.31 rows=1 width=8) (actual time=118491.043..118491.044 rows=1 loops=3)
               Output: PARTIAL count(*)
               Worker 0: actual time=118475.143..118475.144 rows=1 loops=1
               Worker 1: actual time=118476.110..118476.111 rows=1 loops=1
               ->  Parallel Index Only Scan using metadata_status_idx on public.metadata  (cost=0.43..192876.13rows=1008870 width=0) (actual time=71.797..118449.265 rows=809820 loops=3)
                     Output: status
                     Heap Fetches: 552630
                     Worker 0: actual time=75.877..118434.476 rows=761049 loops=1
                     Worker 1: actual time=104.872..118436.647 rows=745770 loops=1
 Planning Time: 592.040 ms
 Execution Time: 118554.839 ms
(17 rows)

corpus=#

Isso parece ser muito semelhante a outras perguntas agora, então estou tentando estratégias de mitigação a partir desta resposta :

VACUUM ANALYZE metadata;A primeira vez COUNT(*)que isso levou 5 segundos, as contagens subsequentes levaram 190 ms.

Outros pensamentos:

Ajudaria se a coluna de status fosse dividida em sua própria tabela, com uma chave estrangeira na metadatatabela?

Nota: Estou me convencendo de que esta pergunta é uma duplicata de várias outras perguntas aqui:

Esta resposta pode conter a melhor solução para este problema:

https://stackoverflow.com/a/7945274/2074605

Conforme solicitado, aqui está uma análise do plano de consulta com buffers:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT COUNT(*) FROM metadata;

                                                                                           QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=80771.95..80771.96 rows=1 width=8) (actual time=26711.481..26716.494 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=293915 read=19595 dirtied=282 written=12
   ->  Gather  (cost=80771.73..80771.94 rows=2 width=8) (actual time=26711.203..26716.488 rows=3 loops=1)
         Output: (PARTIAL count(*))
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=293915 read=19595 dirtied=282 written=12
         ->  Partial Aggregate  (cost=79771.73..79771.74 rows=1 width=8) (actual time=26565.622..26565.623 rows=1 loops=3)
               Output: PARTIAL count(*)
               Buffers: shared hit=293915 read=19595 dirtied=282 written=12
               Worker 0: actual time=26530.890..26530.891 rows=1 loops=1
                 Buffers: shared hit=105264 read=6760 dirtied=145 written=5
               Worker 1: actual time=26530.942..26530.942 rows=1 loops=1
                 Buffers: shared hit=84675 read=7529 dirtied=46 written=2
               ->  Parallel Index Only Scan using metadata_status_idx on public.metadata  (cost=0.43..77241.05 rows=1012275 width=0) (actual time=42.254..26529.232 rows=809820 loops=3)
                     Output: status
                     Heap Fetches: 17185
                     Buffers: shared hit=293915 read=19595 dirtied=282 written=12
                     Worker 0: actual time=59.291..26494.376 rows=815113 loops=1
                       Buffers: shared hit=105264 read=6760 dirtied=145 written=5
                     Worker 1: actual time=31.165..26484.729 rows=1036972 loops=1
                       Buffers: shared hit=84675 read=7529 dirtied=46 written=2
 Planning Time: 98.400 ms
 Execution Time: 26716.529 ms
(25 rows)

Doe Jowns

Asked: 2024-06-15 19:21:42 +0800 CST

Desempenho errático de CLUSTER no PostgreSQL

5

Eu trabalho com PostgreSQL 12 e tenho tabelas particionadas que estou tentando agrupar. Cada partição tem aproximadamente o mesmo tamanho em termos de GB. No entanto, o desempenho pode variar significativamente, variando de 5 a 1.000 minutos de tempo de cluster por partição.

Estou agrupando várias partições em cluster em paralelo e meu banco de dados não está recebendo nenhuma solicitação além das solicitações CLUSTER.

Não entendo completamente os meandros do processo CLUSTER nos bastidores. O que poderia explicar variações tão grandes no tempo de agrupamento e o que posso fazer para melhorar o desempenho?

EDIT: Para ser mais preciso, tenho uma partição de 34 GB agrupada em cluster em 13 minutos e uma partição de 56 GB agrupada em cluster em 1288 minutos (quase um dia). Esses valores de tamanho são o tamanho antes do cluster. Estou recalculando os tamanhos.

Consulta usada para recuperar o tamanho:

SELECT table_name, 
       pg_size_pretty(pg_total_relation_size(table_schema || '."' || table_name || '"')) AS size
FROM information_schema.tables
WHERE table_schema = 'partitionschema' and table_name like 'mytable_%' order by table_name;

Eu não fiz ANALYZE ou VACUUM antes de agrupar.

Tenho E/S de disco de 900 MB/s e 128 GB de RAM (embora apenas 40 pareçam ser usados de acordo com meu painel do datadog). Também não parece que meus processadores sejam uma limitação.

Tenho 300 partições para agrupar, em cada etapa do processo, estou tentando agrupar 8 partições em paralelo. Cada uma dessas partições está em ~ 50 GB

Algumas outras informações:

manutenção_work_mem: 4 GB
trabalho_meme: 64 MB
buffer_compartilhado: 32 GB
max_wal_size: 4 GB

user570286

Asked: 2024-05-08 05:07:23 +0800 CST

Como posso encontrar a linha superior e contá-las com apenas uma varredura?

7

Suponha que eu tenha um esquema como o seguinte:

-- Many rows
CREATE TABLE t1(i INTEGER PRIMARY KEY, c1 INTEGER, c2 INTEGER);

-- t1's rows with c1 even
CREATE VIEW t1_filtered(i, c1, c2) AS
  SELECT i, c1, c2 FROM t1 WHERE c1 % 2 == 0;
-- The real WHERE clause is slightly more complex.

Suponha que essa tabela t1contenha alguns milhões de linhas:

INSERT INTO t1(i, c1, c2)
  SELECT value, random(), random() FROM generate_series(1, 5000000);

Suponha que eu queira obter o índice da linha t1que tem o par mais alto c1e a contagem de linhas com par c1que também tem par c2:

SELECT
  (SELECT i FROM t1_filtered ORDER BY c1 DESC LIMIT 1),
  (SELECT count(*) FROM t1_filtered WHERE c2 % 2 == 0);

A cláusula real ORDER BYé muito mais complexa, mas isto é suficiente para ilustrar o meu problema.

Parece-me que isso deveria ser possível com apenas uma varredura t1, mas EXPLAIN QUERY PLANdiz que esta consulta verifica t1duas vezes:

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  |--SCAN t1
|  `--USE TEMP B-TREE FOR ORDER BY
`--SCALAR SUBQUERY 2
   `--SCAN t1

Se eu unir as duas subconsultas em vez de escrevê-las como colunas de resultados, o plano de consulta será diferente, mas ainda terá duas varreduras de t1:

QUERY PLAN
|--CO-ROUTINE (subquery-1)
|  |--SCAN t1
|  `--USE TEMP B-TREE FOR ORDER BY
|--MATERIALIZE (subquery-2)
|  `--SCAN t1
|--SCAN (subquery-1)
`--SCAN (subquery-2)

Imperativamente, eu esperaria que esta consulta fosse algo como este pseudocódigo:

var top_row = {i: NULL, c1: 0};
var count = 0;
for each {i, c1, c2} in t1:
  if c1 % 2 == 0:
    if c1 > top_row.c1:
      top_row = {i, c1};
    if c2 % 2 == 0:
      count = count + 1;
return {top_row.i, count};

Como posso fazer com que o planejador de consultas veja que isso precisa apenas de uma verificação?

Atualização, 09/05/2024: tentei a consulta proposta por Charlieface . De acordo com EXPLAIN QUERY PLAN, isso faz com que o planejador de consultas use apenas uma varredura de t1...

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  |--SCAN t1
|  |  `--USE TEMP B-TREE FOR ORDER BY
|  `--SCAN (subquery-4)
`--SCAN t

... mas é executado significativamente mais lentamente do que minha SELECT (...), (...)consulta original: testando ambas as consultas no SQLite REPL com .timer on, descobri que (para minha tabela de exemplo t1com 5 milhões de linhas de dados aleatórios) minha consulta original tem um tempo médio de execução de 2,22 segundos com desvio padrão amostral de 0,03 s e esta consulta proposta tem média de 5,57 s com st. dev. 0,23 seg.

Algo que minha intuição imperativa não me sugeriu, mas que o estudo dos planos de consulta "USE TEMP B-TREE FOR ORDER BY" sugeriu foi adicionar um índice em t1(c1). Isso realmente acelera minha consulta original, fazendo com que a média seja de 1,09 s com st. dev. 0,02 seg. No entanto, para minha surpresa, o índice aparentemente faz com que a consulta de Charlieface demore muito mais ou possivelmente até mesmo a torna ininterrupta - interrompi-a depois de esperar 108 segundos e interrompi-a depois de tentar novamente por 32 s, e então o fiz. não tente novamente.

A consulta de Charlieface responde literalmente à minha pergunta, reduzindo o número de varreduras t1para um, mas seu baixo desempenho prático em relação à minha consulta original me faz relutante em aceitá-la como resposta. Espero que isso não seja "mover demais as traves da baliza". Eu marquei isso como query-performance, então o desempenho fez parte da minha pergunta desde o início.

Atualização, 09/05/2024 nº 2: Com CREATE INDEX t1_c1 ON t1(c1), o plano de consulta para minha consulta original tornou-se

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  `--SCAN t1 USING COVERING INDEX t1_c1
`--SCALAR SUBQUERY 2
   `--SCAN t1

e o plano de consulta para a consulta de Charlieface tornou-se

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  `--SCAN t1 USING INDEX t1_c1
|  `--SCAN (subquery-4)
`--SCAN t

Com CREATE INDEX index_per_comment660077_339327 ON t1 (c1 DESC) WHERE (c1 % 2 = 0)o comentário do Charlieface (SQLite não suporta INCLUDE), os planos de consulta tornam-se, respectivamente,

QUERY PLAN
|--SCAN CONSTANT ROW
|--SCALAR SUBQUERY 1
|  `--SCAN t1 USING COVERING INDEX index_per_comment660077_339327
`--SCALAR SUBQUERY 2
   `--SCAN t1 USING INDEX index_per_comment660077_339327

QUERY PLAN
|--CO-ROUTINE t
|  |--CO-ROUTINE (subquery-4)
|  |  `--SCAN t1 USING INDEX index_per_comment660077_339327
|  `--SCAN (subquery-4)
`--SCAN t

Recomendação de banco de dados/índice de pesquisa: combinar intervalos de tempo de diferentes categorias

Sql Server: Consulta para analisar e validar códigos

Consulta para excluir registros com eff_date menor em uma tabela grande com 400 milhões de registros

Estatísticas de atualização automática - Plano de consulta ruim

Azure SQL: problemas de desempenho do hierarchyid

Em um "select * where col=x" onde x é uma coluna não ordenável e não indexada, um banco de dados oferece algum ganho de desempenho em relação a um arquivo simples?

Como otimizar múltiplos WHEN que estão usando diferentes strings estáticas?

Qual é a causa dessa consulta lenta intermitente ao contar linhas em uma única coluna de texto indexada no PostgreSQL?

Desempenho errático de CLUSTER no PostgreSQL

Como posso encontrar a linha superior e contá-las com apenas uma varredura?

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Perguntas[query-performance](dba)