Postgres Query Optimization, funções de alto custo

Question

Atualmente estou trabalhando com uma tabela postgres que se parece com isso (postgres12)

create table if not exists asset (
  id text,
  symbol text not null,
  name text not null
  primary key (id)
);
create table if not exists latest_value (
  timestamp bigint,
  asset text,
  price decimal null,
  market_cap decimal null,
  primary key (asset),
  foreign key (asset)
    references asset (id)
    on delete cascade
);
create table if not exists value_aggregation (
  context aggregation_context,
  timestamp bigint,
  asset text,
  price jsonb null,
  market_cap jsonb null,
  primary key (context, timestamp, asset),
  foreign key (asset)
      references asset (id)
      on delete cascade
) partition by list (context);

create table if not exists value_aggregation_hour
partition of value_aggregation
for values in ('hour');

create index if not exists value_aggregation_timestamp_index
  on value_aggregation using brin(timestamp)
  with (autosummarize=true);

A tabela value_aggregation_hourtem aproximadamente 2 milhões de linhas. A pricecoluna consiste em um jsonb com atributos como open, close, avg

Agora o problema:

A consulta a seguir leva muito tempo.

WITH base_table AS
  (SELECT asset, timestamp, market_cap, price
  FROM latest_value
  ORDER BY market_cap DESC
  LIMIT 50
  OFFSET 0)
SELECT asset.name, asset.symbol, asset.id, asset.market_data, asset.meta_data, timestamp, market_cap, price, spark.sparkline
FROM base_table LEFT JOIN (
  SELECT asset, array_agg(CAST(price->>'open' AS decimal) ORDER BY timestamp ASC) AS sparkline
  FROM value_aggregation
  WHERE context = 'hour'
  AND timestamp > extract(epoch from (now() - INTERVAL '7d'))
  AND asset IN (
    SELECT asset
    FROM base_table)
GROUP BY asset
) spark ON base_table.asset = spark.asset
INNER JOIN asset ON base_table.asset = asset.id;

O queryplan resultante se parece com isto:

Merge Left Join  (cost=234610.64..234774.05 rows=494 width=1740) (actual time=9173.660..9176.986 rows=50 loops=1)
  Merge Cond: (base_table.asset = value_aggregation_hour.asset)
  CTE base_table
    ->  Limit  (cost=140.48..140.61 rows=50 width=71) (actual time=2.040..2.051 rows=50 loops=1)
          ->  Sort  (cost=140.48..145.48 rows=2001 width=71) (actual time=2.039..2.043 rows=50 loops=1)
                Sort Key: latest_value.market_cap DESC
                Sort Method: top-N heapsort  Memory: 36kB
                ->  Seq Scan on latest_value  (cost=0.00..74.01 rows=2001 width=71) (actual time=0.011..0.536 rows=2001 loops=1)
  ->  Sort  (cost=377.41..377.54 rows=50 width=1740) (actual time=2.582..2.660 rows=50 loops=1)
        Sort Key: base_table.asset
        Sort Method: quicksort  Memory: 127kB
        ->  Nested Loop  (cost=0.28..376.00 rows=50 width=1740) (actual time=2.071..2.434 rows=50 loops=1)
              ->  CTE Scan on base_table  (cost=0.00..1.00 rows=50 width=232) (actual time=2.042..2.068 rows=50 loops=1)
              ->  Index Scan using asset_pkey on asset  (cost=0.28..7.50 rows=1 width=1508) (actual time=0.006..0.006 rows=1 loops=50)
                    Index Cond: (id = base_table.asset)
  ->  GroupAggregate  (cost=234092.62..234226.12 rows=1977 width=54) (actual time=9171.070..9174.268 rows=15 loops=1)
        Group Key: value_aggregation_hour.asset
        ->  Sort  (cost=234092.62..234110.75 rows=7253 width=203) (actual time=9167.909..9168.235 rows=2501 loops=1)
              Sort Key: value_aggregation_hour.asset
              Sort Method: quicksort  Memory: 761kB
              ->  Hash Semi Join  (cost=1.62..233627.54 rows=7253 width=203) (actual time=8985.832..9163.859 rows=2501 loops=1)
                    Hash Cond: (value_aggregation_hour.asset = base_table_1.asset)
                    ->  Seq Scan on value_aggregation_hour  (cost=0.00..232792.39 rows=286795 width=203) (actual time=8983.255..9112.164 rows=304163 loops=1)
                          Filter: ((\"timestamp\" > '1597855853329'::bigint) AND (context = 'hour'::aggregation_context))
                          Rows Removed by Filter: 2228311
                    ->  Hash  (cost=1.00..1.00 rows=50 width=32) (actual time=0.032..0.032 rows=50 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 11kB
                          ->  CTE Scan on base_table base_table_1  (cost=0.00..1.00 rows=50 width=32) (actual time=0.004..0.014 rows=50 loops=1)
Planning Time: 1.203 ms
Execution Time: 9177.185 ms

Percebi que o planejador de consulta não usa o índice criado value_aggregation_houre queria saber o porquê. Após algumas pesquisas no Google, desativei o seqscan durante a depuração, executei a consulta novamente com explain analyzee, em seguida, saiu o seguinte plano de consulta:

Merge Left Join  (cost=10000237612.82..10000237776.37 rows=494 width=1740) (actual time=212.122..215.857 rows=50 loops=1)
  Merge Cond: (base_table.asset = value_aggregation_hour.asset)
  CTE base_table
    ->  Limit  (cost=10000000140.48..10000000140.61 rows=50 width=71) (actual time=1.745..1.756 rows=50 loops=1)
          ->  Sort  (cost=10000000140.48..10000000145.48 rows=2001 width=71) (actual time=1.744..1.748 rows=50 loops=1)
                Sort Key: latest_value.market_cap DESC
                Sort Method: top-N heapsort  Memory: 36kB
                ->  Seq Scan on latest_value  (cost=10000000000.00..10000000074.01 rows=2001 width=71) (actual time=0.006..0.555 rows=2001 loops=1)
  ->  Sort  (cost=377.41..377.54 rows=50 width=1740) (actual time=2.240..2.250 rows=50 loops=1)
        Sort Key: base_table.asset
        Sort Method: quicksort  Memory: 127kB
        ->  Nested Loop  (cost=0.28..376.00 rows=50 width=1740) (actual time=1.771..2.090 rows=50 loops=1)
              ->  CTE Scan on base_table  (cost=0.00..1.00 rows=50 width=232) (actual time=1.746..1.773 rows=50 loops=1)
              ->  Index Scan using asset_pkey on asset  (cost=0.28..7.50 rows=1 width=1508) (actual time=0.006..0.006 rows=1 loops=50)
                    Index Cond: (id = base_table.asset)
  ->  GroupAggregate  (cost=237094.80..237228.44 rows=1977 width=54) (actual time=209.877..213.542 rows=15 loops=1)
        Group Key: value_aggregation_hour.asset
        ->  Sort  (cost=237094.80..237112.96 rows=7262 width=203) (actual time=209.618..210.065 rows=2501 loops=1)
              Sort Key: value_aggregation_hour.asset
              Sort Method: quicksort  Memory: 761kB
              ->  Hash Semi Join  (cost=111.95..236629.08 rows=7262 width=203) (actual time=0.868..206.008 rows=2501 loops=1)
                    Hash Cond: (value_aggregation_hour.asset = base_table_1.asset)
                    ->  Bitmap Heap Scan on value_aggregation_hour  (cost=110.32..235792.92 rows=287144 width=203) (actual time=0.758..155.291 rows=304163 loops=1)
                          Recheck Cond: (\"timestamp\" > '1597855085099'::bigint)
                          Rows Removed by Index Recheck: 215
                          Filter: (context = 'hour'::aggregation_context)
                          Heap Blocks: lossy=23414
                          ->  Bitmap Index Scan on value_aggregation_hour_timestamp_idx  (cost=0.00..38.54 rows=287851 width=0) (actual time=0.698..0.698 rows=234240 loops=1)
                                Index Cond: (\"timestamp\" > '1597855085099'::bigint)
                    ->  Hash  (cost=1.00..1.00 rows=50 width=32) (actual time=0.025..0.025 rows=50 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 11kB
                          ->  CTE Scan on base_table base_table_1  (cost=0.00..1.00 rows=50 width=32) (actual time=0.001..0.007 rows=50 loops=1)
Planning Time: 1.532 ms
Execution Time: 216.114 ms

Os custos finais são bem altos, mas suponho que seja porque não há índice latest_valueativado e ele precisa usar um seqscan (desativado = custos ultra altos?).
Mas agora ele usa o índice de value_aggregation_houre é muuuito mais rápido.
Como desativar o seqscan não é uma opção válida, exceto para depuração, como posso fazer isso funcionar corretamente? Posso otimizar a consulta? Talvez mudar algo do BRIN, então ele usa isso em vez de um seqscan?
Ou seria mais adequado um ajuste de parâmetros, para que as funções de custo sejam calculadas de forma diferente? Estou usando uma instância RDS postgres db.t3.small com a configuração padrão.

Atualização nº 1 :
remover a AND asset IN (...)subconsulta (redundante?) aumenta o tempo de execução em um segundo (seqscan ativado), aqui está o plano de consulta resultante:

Merge Left Join  (cost=285605.54..289542.19 rows=494 width=1589) (actual time=10213.724..10561.884 rows=50 loops=1)"
  Merge Cond: (latest_value.asset = value_aggregation_hour.asset)"
  ->  Sort  (cost=517.65..517.77 rows=50 width=1579) (actual time=2.315..2.347 rows=50 loops=1)"
        Sort Key: latest_value.asset"
        Sort Method: quicksort  Memory: 127kB"
        ->  Nested Loop  (cost=140.89..516.24 rows=50 width=1579) (actual time=1.646..2.160 rows=50 loops=1)"
              ->  Limit  (cost=140.61..140.74 rows=50 width=71) (actual time=1.623..1.634 rows=50 loops=1)"
                    ->  Sort  (cost=140.61..145.62 rows=2004 width=71) (actual time=1.622..1.626 rows=50 loops=1)"
                          Sort Key: latest_value.market_cap DESC"
                          Sort Method: top-N heapsort  Memory: 36kB"
                          ->  Seq Scan on latest_value  (cost=0.00..74.04 rows=2004 width=71) (actual time=0.006..0.507 rows=2004 loops=1)"
              ->  Index Scan using asset_pkey on asset  (cost=0.28..7.50 rows=1 width=1508) (actual time=0.010..0.010 rows=1 loops=50)"
                    Index Cond: (id = latest_value.asset)"
  ->  GroupAggregate  (cost=285087.89..288994.63 rows=1977 width=54) (actual time=10196.939..10558.723 rows=1795 loops=1)"
        Group Key: value_aggregation_hour.asset"
        ->  Sort  (cost=285087.89..285734.90 rows=258802 width=203) (actual time=10196.652..10291.799 rows=295051 loops=1)"
              Sort Key: value_aggregation_hour.asset"
              Sort Method: external merge  Disk: 66000kB"
              ->  Seq Scan on value_aggregation_hour  (cost=0.00..236164.67 rows=258802 width=203) (actual time=8901.696..9056.748 rows=304558 loops=1)"
                    Filter: ((\"timestamp\" > '1597925634239'::bigint) AND (context = 'hour'::aggregation_context))"
                    Rows Removed by Filter: 2264599"
Planning Time: 1.149 ms"
Execution Time: 10573.183 ms"

Atualização nº 2:

alterar a consulta para sugestão lateral de junção esquerda a_horse_with_no_name resultou em:

Nested Loop Left Join  (cost=141.45..576626.74 rows=6550 width=1589) (actual time=68.291..1313.768 rows=50 loops=1)
  ->  Nested Loop  (cost=140.89..516.24 rows=50 width=1579) (actual time=3.897..5.104 rows=50 loops=1)
        ->  Limit  (cost=140.61..140.74 rows=50 width=71) (actual time=3.855..3.931 rows=50 loops=1)
              ->  Sort  (cost=140.61..145.62 rows=2004 width=71) (actual time=3.853..3.900 rows=50 loops=1)
                    Sort Key: latest_value.market_cap DESC
                    Sort Method: top-N heapsort  Memory: 37kB
                    ->  Seq Scan on latest_value  (cost=0.00..74.04 rows=2004 width=71) (actual time=0.016..0.915 rows=2004 loops=1)
        ->  Index Scan using asset_pkey on asset  (cost=0.28..7.50 rows=1 width=1508) (actual time=0.017..0.017 rows=1 loops=50)
              Index Cond: (id = latest_value.asset)
  ->  GroupAggregate  (cost=0.56..11519.59 rows=131 width=54) (actual time=26.169..26.169 rows=0 loops=50)
        Group Key: value_aggregation_hour.asset
        ->  Index Scan using value_aggregation_hour_pkey on value_aggregation_hour  (cost=0.56..11516.32 rows=131 width=203) (actual time=18.780..26.105 rows=50 loops=50)
              Index Cond: ((context = 'hour'::aggregation_context) AND (\"timestamp\" > '1597926623087'::bigint) AND (asset = latest_value.asset))
Planning Time: 1.066 ms
Execution Time: 1320.452 ms

Grande melhoria, funcionaria bem. Mas isso ainda não é tão bom quanto usar o índice BRIN na consulta inicial.

Answer 1

O PostgreSQL estima a varredura sequencial value_aggregation_hourum pouco mais barata que a varredura de índice (233.000 vs. 236.000), enquanto na realidade é muito mais barata.

A estimativa de contagem de linhas é muito boa, então o problema provavelmente é que o PostgreSQL tem uma ideia errada sobre sua máquina. Você pode tentar melhorar isso:

• definido effective_cache_sizepara a quantidade de memória disponível para armazenar dados em cache ( shared_buffers+ cache do sistema de arquivos).

  Valores maiores diminuem o estimado. custo de varreduras de índice.

• definido random_page_costpara um valor mais baixo. Se o acesso aleatório for tão rápido quanto o acesso sequencial em seu sistema de armazenamento, use o valor 1.

  Valores mais baixos diminuem o estimado. custo de varreduras de índice.