Início / dba / Perguntas / 184087
Accepted
raphael
Asked: 2017-08-09 11:22:25 +0800 CST

Restrição de partição não usada para junções envolvendo tabelas particionadas por carimbo de data/hora

  • 772

Eu tenho uma estrutura de tabela particionada como:

CREATE TABLE measurements (
    sensor_id bigint,
    tx timestamp,
    measurement int
);

CREATE TABLE measurements_201201(
    CHECK (tx >= '2012-01-01 00:00:00'::timestamp without time zone 
       AND tx < ('2012-01-01 00:00:00'::timestamp without time zone + '1 mon'::interval))    
)INHERITS (measurements);
CREATE INDEX ON measurements_201201(sensor_id);
CREATE INDEX ON measurements_201201(tx);
CREATE INDEX ON measurements_201201(sensor_id, tx);
....

E assim por diante. Cada tabela tem aproximadamente 20 milhões de linhas.

Se eu consultar uma amostra de sensores e uma amostra de timestamps na WHEREcláusula, o plano de consulta mostra as tabelas corretas sendo selecionadas e os índices sendo usados, por exemplo:

SELECT *
FROM measurements
INNER JOIN sensors TABLESAMPLE BERNOULLI (0.01) USING (sensor_id)
WHERE tx BETWEEN '2015-01-04 05:00' AND '2015-01-04 06:00' 
    OR tx BETWEEN '2015-02-04 05:00' AND '2015-02-04 06:00' 
    OR tx BETWEEN '2014-03-05 05:00' AND '2014-04-07 06:00' ;

No entanto, se eu usar um CTE, ou colocar os valores de timestamp em uma tabela (não mostrado, mesmo com índices na tabela temporária).

WITH sensor_sample AS(
    SELECT sensor_id, start_ts, end_ts
    FROM sensors TABLESAMPLE BERNOULLI (0.01)
    CROSS JOIN (VALUES (TIMESTAMP '2015-01-04 05:00', TIMESTAMP '2015-01-04 06:00'),
        (TIMESTAMP '2015-02-04 05:00', TIMESTAMP '2015-02-04 06:00'),
        (TIMESTAMP  '2014-03-05 05:00', '2014-04-07 06:00') ) tstamps(start_ts, end_ts)
)

Algo como o abaixo

SET constraint_exclusion = on;
SELECT * FROM measurements
INNER JOIN sensor_sample USING (sensor_id)
WHERE tx BETWEEN start_ts AND end_ts

Executa uma varredura de índice em cada tabela. O que ainda é relativamente rápido, mas com o aumento da complexidade das consultas, isso pode se transformar em varreduras seq que acabarão sendo muito lentas para recuperar cerca de 40 mil linhas de um subconjunto limitado de tabelas particionadas (4-5 de 50).

Estou preocupado que algo assim seja o problema.

Para expressões não triviais, você precisa repetir a condição mais ou menos literal nas consultas para fazer o planejador de consultas do Postgres entender que pode confiar na restrição CHECK. Mesmo que pareça redundante!

Como posso melhorar o particionamento e a estrutura de consulta para reduzir a probabilidade de executar verificações seq em todos os meus dados?

postgresql partitioning

1 respostas

  1. Best Answer

    A exclusão baseada em restrições [CBE] é executada no estágio inicial do planejamento da consulta, logo após a consulta ser analisada, mapeada para as relações reais e reescrita. ( internos , estágio do planejador/otimizador)

    O planejador não pode assumir nenhum conteúdo da tabela "sensor_sample".

    Portanto, a menos que você tenha valores codificados na consulta, o planejador não excluirá "partições".

    Acho que o que acontece com a variante CTE... o planejador é restrito porque você usa TABLESAMPLE e toda a subconsulta pode ser tratada como volátil, mesmo que os literais na subconsulta sejam estáticos. ( esse é apenas o meu palpite, não sou especialista em código de planejador )

    Pelo lado positivo, a varredura de índice com resultado negativo é incrivelmente rápida. (varredura de página única no máximo!) então, a menos que você tenha mais de 10.000 partições, eu não me incomodaria.

    Então, respondendo diretamente à sua pergunta:

    • Você não pode melhorar muito mais essa estrutura de dados.

    • Com relação às varreduras de índice - elas são baratas;

    • Em relação às varreduras sequenciais - elas são evitadas quando possível, como você vê em seus próprios exemplos.

    • 1

relate perguntas