Mad Scientist's questions

Percebi que o desempenho de uma consulta envolvendo uma coluna jsonb variou significativamente entre as execuções de VACUUM ANALYZE durante o teste. Eu recebo planos de execução completamente diferentes aparentemente aleatoriamente depois de analisar a tabela.

Estou usando o Postgres 9.6 aqui. A configuração para meus testes é a seguinte, estou inserindo uma única chave "x" na coluna jsonb "params" com valores entre 1 e 6, sendo 1 o valor mais raro e 6 o mais comum. Eu também tenho uma coluna int regular "single_param" que contém a mesma distribuição de valores para comparação.:

CREATE TABLE test_data (
    id      serial,
    single_param    int,
    params      jsonb
);

INSERT INTO test_data
SELECT 
    generate_series(1, 1000000) AS id, 
    floor(log(random() * 9999999 + 1)) AS single_param,
    json_build_object(
        'x', floor(log(random() * 9999999 + 1))
    ) AS params;

CREATE INDEX idx_test_btree ON test_data (cast(test_data.params->>'x' AS int));
CREATE INDEX idx_test_gin ON test_data USING GIN (params);
CREATE INDEX ON test_data(id)
CREATE INDEX ON test_data(single_param)

A consulta que estou testando é uma consulta típica para paginar resultados, estou classificando por id e limitando a saída às primeiras 50 linhas.

SELECT * FROM test_data where (params->>'x')::int = 1 ORDER BY id DESC LIMIT 50;

Eu recebo uma das duas saídas de análise de explicação aleatoriamente após a execução VACUUM ANALYZE:

Limit  (cost=0.42..836.59 rows=50 width=33) (actual time=39.679..410.292 rows=10 loops=1)
  ->  Index Scan Backward using test_data_id_idx on test_data  (cost=0.42..44317.43 rows=2650 width=33) (actual time=39.678..410.283 rows=10 loops=1)
        Filter: (((params ->> 'x'::text))::integer = 1)
        Rows Removed by Filter: 999990"
Planning time: 0.106 ms
Execution time: 410.314 ms

ou

Limit  (cost=8.45..8.46 rows=1 width=33) (actual time=0.032..0.034 rows=10 loops=1)
  ->  Sort  (cost=8.45..8.46 rows=1 width=33) (actual time=0.032..0.032 rows=10 loops=1)
        Sort Key: id DESC
        Sort Method: quicksort  Memory: 25kB
        ->  Index Scan using idx_test_btree on test_data  (cost=0.42..8.44 rows=1 width=33) (actual time=0.007..0.016 rows=10 loops=1)
              Index Cond: (((params ->> 'x'::text))::integer = 1)
Planning time: 0.320 ms
Execution time: 0.052 ms

A diferença é que a estimativa para o número de colunas correspondentes à cláusula where é diferente entre os dois planos. Na primeira a estimativa é de 2650 linhas, na segunda 1 linha enquanto o número real é de 10 linhas.

A seguinte versão da consulta que pode potencialmente usar o índice GIN parece usar uma estimativa padrão para a coluna json de 1%, o que também resulta no plano de consulta incorreto como acima:

SELECT * FROM test_data where params @> '{"x": 1}' ORDER BY id DESC LIMIT 50;

Minha suposição original era que o Postgres não teria nenhuma estatística na coluna jsonb e sempre usaria uma estimativa como faz para a consulta usando o @>operador. Mas para a consulta que é escrita poder usar o índice btree que criei, ela usa estimativas diferentes. Às vezes, esses são bons o suficiente, e às vezes são ruins.

De onde vêm essas estimativas? Eu acho que eles são algum tipo de estatística que o Postgres cria com o índice. Para estatísticas de coluna existe a opção de coletar estatísticas mais precisas, existe algo assim para essas estatísticas aqui? Ou alguma outra maneira de fazer com que o Postgres escolha o melhor plano no meu caso?

Estou usando a extensão Postgres de tabelas temporais ( http://pgxn.org/dist/temporal_tables/ ) no Postgres 9.5.4 no Windows. Isso funciona bem para a maioria dos casos, mas estou enfrentando um problema estranho com colunas de matriz.

Eu criei algumas etapas mínimas para reproduzir isso, a configuração da tabela e a extensão das tabelas temporais é a seguinte:

CREATE EXTENSION IF NOT EXISTS temporal_tables;

DROP TABLE IF EXISTS test;
DROP TABLE IF EXISTS test_history;

CREATE TABLE test
(
  id SERIAL PRIMARY KEY,
  a integer,
  directories text[],
  sys_period tstzrange NOT NULL
);

CREATE TABLE test_history (LIKE test);
CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON test FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'test_history', true);

E em transações separadas os dois comandos a seguir:

INSERT INTO test(a) VALUES (1);

UPDATE test SET a = 5 WHERE id = 1; 

Estou tendo o erro a seguir:

ERRO: a coluna "diretórios" da relação "teste" é do tipo text[] mas a coluna "diretórios" da relação de histórico "test_history" é do tipo text[]

O erro não faz sentido para mim, ambas as colunas têm o mesmo tipo e o erro até afirma isso. Isso só acontece quando uma coluna text[] está presente na tabela. Não é necessário escrever nessa coluna, apenas tem que estar presente. Se eu não criar a coluna array, não há erro.

A extensão não menciona nada sobre incompatibilidade com arrays, e eu esperaria que esse tipo de restrição fosse grande o suficiente para ser mencionado. Eu estou querendo saber se há algo errado com a forma como eu configuro as tabelas, ou se estou faltando mais alguma coisa aqui. Existe algum motivo subjacente pelo qual duas colunas de matriz text[] seriam de tipo diferente?

Alguma idéia do que realmente causa esse erro e como eu poderia me livrar dele?

Eu tenho uma consulta onde quero buscar as primeiras linhas dos conjuntos de dados da tabela ordenados pela coluna date_added. A coluna classificada por é indexada, portanto, a versão básica desta tabela é muito rápida:

SELECT datasets.id FROM datasets ORDER BY date_added LIMIT 25

"Limit  (cost=0.28..6.48 rows=25 width=12) (actual time=0.040..0.092 rows=25 loops=1)"
"  ->  Index Scan using datasets_date_added_idx2 on datasets  (cost=0.28..1244.19 rows=5016 width=12) (actual time=0.037..0.086 rows=25 loops=1)"
"Planning time: 0.484 ms"
"Execution time: 0.139 ms"

Mas eu tenho um problema quando faço a consulta um pouco mais complicada. Desejo juntar outra tabela representando um relacionamento muitos para muitos e agregar os resultados em uma coluna de matriz. Para fazer isso, preciso adicionar uma cláusula GROUP BY id:

SELECT datasets.id FROM datasets GROUP BY datasets.id ORDER BY date_added LIMIT 25

"Limit  (cost=551.41..551.47 rows=25 width=12) (actual time=9.926..9.931 rows=25 loops=1)"
"  ->  Sort  (cost=551.41..563.95 rows=5016 width=12) (actual time=9.924..9.926 rows=25 loops=1)"
"        Sort Key: date_added"
"        Sort Method: top-N heapsort  Memory: 26kB"
"        ->  HashAggregate  (cost=359.70..409.86 rows=5016 width=12) (actual time=7.016..8.604 rows=5016 loops=1)"
"              Group Key: datasets_id"
"              ->  Seq Scan on datasets  (cost=0.00..347.16 rows=5016 width=12) (actual time=0.009..1.574 rows=5016 loops=1)"
"Planning time: 0.502 ms"
"Execution time: 10.235 ms"

Apenas adicionando a cláusula GROUP BY, a consulta agora faz uma varredura completa da tabela de conjuntos de dados em vez de usar o índice na coluna date_added como anteriormente.

Uma versão simplificada da consulta real que quero fazer é a seguinte:

SELECT 
    datasets.id,
    array_remove(array_agg(other_table.some_column), NULL) AS other_table
FROM datasets 
LEFT JOIN other_table 
    ON other_table.id = datasets.id
GROUP BY datasets.id 
ORDER BY date_added 
LIMIT 25

Por que a cláusula GROUP BY faz com que o índice seja ignorado e força uma varredura completa da tabela? E existe uma maneira de reescrever essa consulta para que ela use o índice na coluna pela qual ela é classificada?

Estou usando o Postgres 9.5.4 no Windows, a tabela em questão tem atualmente 5000 linhas, mas poderia ter algumas centenas de milhares. Executei ANALYZE manualmente em ambas as tabelas antes do EXPLAIN ANALYZE.

Definições da tabela:

CREATE TABLE public.datasets
(
  id integer NOT NULL DEFAULT nextval('datasets_id_seq'::regclass),
  date_added timestamp with time zone,
  ...
  CONSTRAINT datasets_pkey PRIMARY KEY (id)
)

CREATE TABLE public.other_table
(
  id integer NOT NULL,
  some_column integer NOT NULL,
  CONSTRAINT other_table_pkey PRIMARY KEY (id, some_column)
)

A saída de \d datasetscom colunas irrelevantes anonimizadas:

                                                   Table "public.datasets"
             Column              |           Type           |                           Modifiers
---------------------------------+--------------------------+------------------------------------------------------
 id                              | integer                  | not null default nextval('datasets_id_seq'::regclass)
 key                             | text                     |
 date_added                      | timestamp with time zone |
 date_last_modified              | timestamp with time zone |
 *****                           | integer                  |
 ********                        | boolean                  | default false
 *****                           | boolean                  | default false
 ***************                 | integer                  |
 *********************           | integer                  |
 *********                       | boolean                  | default false
 ********                        | integer                  |
 ************                    | integer                  |
 ************                    | integer                  |
 ****************                | timestamp with time zone |
 ************                    | text                     | default ''::text
 *****                           | text                     |
 *******                         | integer                  |
 *********                       | integer                  |
 **********************          | text                     | default ''::text
 *******************             | text                     |
 ****************                | integer                  |
 **********************          | text                     | default ''::text
 *******************             | text                     | default ''::text
 **********                      | integer                  |
 ***********                     | text                     |
 ***********                     | text                     |
 **********************          | integer                  |
 ******************************* | text                     | default ''::text
 ************************        | text                     | default ''::text
 ***********                     | integer                  | default 0
 *************                   | text                     |
 *******************             | integer                  |
 ****************                | integer                  | default 0
 ***************                 | text                     |
 **************                  | text                     |
Indexes:
    "datasets_pkey" PRIMARY KEY, btree (id)
    "datasets_date_added_idx" btree (date_added)
    "datasets_*_idx" btree (*)
    "datasets_*_idx" btree (*)
    "datasets_*_idx" btree (*)
    "datasets_*_idx" btree (*)
    "datasets_*_idx" btree (*)
    "datasets_*_idx1" btree (*)
    "datasets_*_idx" btree (*)