PostgreSQL 9.2

Estou tentando entender a diferença entre Hash Semi Joine apenas Hash Join.

Aqui estão duas perguntas:

EU

EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT
customerid FROM customers WHERE state='MD');

Hash Semi Join  (cost=740.34..994.61 rows=249 width=30) (actual time=2.684..4.520 rows=120 loops=1)
  Hash Cond: (orders.customerid = customers.customerid)
  ->  Seq Scan on orders  (cost=0.00..220.00 rows=12000 width=30) (actual time=0.004..0.743 rows=12000 loops=1)
  ->  Hash  (cost=738.00..738.00 rows=187 width=4) (actual time=2.664..2.664 rows=187 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 7kB
        ->  Seq Scan on customers  (cost=0.00..738.00 rows=187 width=4) (actual time=0.018..2.638 rows=187 loops=1)
              Filter: ((state)::text = 'MD'::text)
              Rows Removed by Filter: 19813

II

EXPLAIN ANALYZE SELECT * FROM orders o JOIN customers c ON o.customerid = c.customerid WHERE c.state = 'MD'

Hash Join  (cost=740.34..1006.46 rows=112 width=298) (actual time=2.831..4.762 rows=120 loops=1)
  Hash Cond: (o.customerid = c.customerid)
  ->  Seq Scan on orders o  (cost=0.00..220.00 rows=12000 width=30) (actual time=0.004..0.768 rows=12000 loops=1)
  ->  Hash  (cost=738.00..738.00 rows=187 width=268) (actual time=2.807..2.807 rows=187 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 37kB
        ->  Seq Scan on customers c  (cost=0.00..738.00 rows=187 width=268) (actual time=0.018..2.777 rows=187 loops=1)
              Filter: ((state)::text = 'MD'::text)
              Rows Removed by Filter: 19813

Como pode ser visto, a única diferença nos planos é que no primeiro caso o hastable consome 7kB, mas no segundo 37kBe que o nó é Hash Semi Join.

Mas não entendo a diferença no tamanho da tabela de hash. O Hashnó usa perfeitamente o mesmo Seq Scannó com o mesmo arquivo Filter. Por que existe a diferença?

Eu tenho a tabela customerstendo customerid SERIALcomo chave primária. Inicialmente, eu tinha a seguinte estatística para essa coluna:

  attname   n_distinct    correlation
customerid       -1         -0.393365

A seguinte consulta

EXPLAIN ANALYZE SELECT * FROM customers WHERE customerid < 10

produziu o seguinte plano analisado:

Index Scan using customers_pkey on customers  (cost=0.00..35.46 rows=9 width=268) (actual time=0.003..0.010 rows=9 loops=1)
  Index Cond: (customerid < 10)
Total runtime: 0.029 ms

Observe que o custo para obter todas as linhas é 35.46. Agora eu corri

CLUSTER customers USING customers_pkey;
ANALYZE;

e o correlationtornou-se igual a 1. Depois de executar a consulta mais uma vez, obtive o seguinte plano analisado:

Index Scan using customers_pkey on customers  (cost=0.00..8.41 rows=9 width=268) (actual time=0.003..0.005 rows=9 loops=1)
  Index Cond: (customerid < 10)
Total runtime: 0.024 ms

Observe que o custo diminuiu mais de 4 vezes ( 8.41agora). Minha pergunta é como exatamente o custo ranged index scandepende da correlação? Como é computado? Seria bom se você indicasse algumas referências à documentação do postgresql.

PostgreSQL 9.4

Eu tenho uma tabela chamada que tem um inteiro customersde coluna . incomeDepois de correr ANALYZEcontra ele, obtive a seguinte estatística para a coluna:

       most_common_vals
{20000,80000,40000,60000,100000}

Agora, executo a seguinte consulta simples EXPLAIN ANALYZE SELECT * FROM customers WHERE income=123123para entender a estimativa de contagem de linhas. Resultado:

Seq Scan on customers  (cost=0.00..738.00 rows=1 width=268) (actual time=4.669..4.669 rows=0 loops=1)
  Filter: (income = 123123)
  Rows Removed by Filter: 20000

Como o otimizador não tem estatística para o incomevalor 123123, ele fez uma estimativa 0.5%do tamanho da tabela. Portanto, a contagem de linhas estimada deveria ter sido 500. Mas o otimizador retornou o arquivo 1. Por quê? Talvez eu não tenha entendido o processo de estimativa de contagem de linhas de valores desconhecidos?

Você não poderia explicar um pouco?

Vou tentar explicar meus mal-entendidos com o seguinte exemplo.

Não entendi os fundamentos do Bitmap Heap Scan Node. Considere a consulta SELECT customerid, username FROM customers WHERE customerid < 1000 AND username <'user100';cujo plano é este:

Bitmap Heap Scan on customers  (cost=25.76..61.62 rows=10 width=13) (actual time=0.077..0.077 rows=2 loops=1)
  Recheck Cond: (((username)::text < 'user100'::text) AND (customerid < 1000))
  ->  BitmapAnd  (cost=25.76..25.76 rows=10 width=0) (actual time=0.073..0.073 rows=0 loops=1)
        ->  Bitmap Index Scan on ix_cust_username  (cost=0.00..5.75 rows=200 width=0) (actual time=0.006..0.006 rows=2 loops=1)
              Index Cond: ((username)::text < 'user100'::text)
        ->  Bitmap Index Scan on customers_pkey  (cost=0.00..19.75 rows=1000 width=0) (actual time=0.065..0.065 rows=999 loops=1)
              Index Cond: (customerid < 1000)

Minha compreensão deste nó :

Conforme explicado lá , os bitmap heap scanblocos de tabela de leitura em ordem sequencial, portanto, não produz sobrecarga de acesso à tabela aleatória que acontece ao fazer apenas Index Scan.

Depois Index Scande feito, o PostgreSQL não sabe como buscar as linhas de forma otimizada, para evitar o desnecessário heap blocks reads(ou hitsse houver um hot cache). Então, para descobrir isso, ele gera a estrutura ( Bitmap Index Scan) chamada bitmapque no meu caso está sendo gerada gerando dois bitmaps dos índices e executando BITWISE AND. Uma vez que o bitmap foi gerado, ele agora pode ler a tabela de forma otimizada em uma ordem sequencial, evitando arquivos desnecessários heap I/O-operations.

Esse é o lugar onde muitas perguntas vêm.

PERGUNTA: Temos apenas um bitmap. Como o PostgreSQL sabe por apenas um bitmap qualquer coisa sobre a ordem física das linhas? Ou gera o bitmap para que qualquer elemento dele possa ser mapeado facilmente para o ponteiro de uma página? Se sim, isso explica tudo, mas é apenas minha suposição.

Então, podemos dizer simplesmente que bitmap heap scan -> bitmap index scané como uma varredura sequencial, mas apenas da parte apropriada da tabela?

Eu tenho a seguinte tabela no PostgreSQL 9.4:

CREATE TABLE dpa(
  id serial NOT NULL,
  currency_id integer,
  amount numeric(14,3),
  date timestamp without time zone,
  plat_id integer,
  pl_id integer,
  p_id integer,
  CONSTRAINT dpa_pkey PRIMARY KEY (id), 
)

e configurações:

work_mem = 128MB
table_size = 16 MB

E índice:

CREATE INDEX idx1
  ON dpa
  USING btree
  (plat_id, p_id, pl_id, currency_id, date DESC NULLS LAST, amount)

A tabela consiste em aproximadamente 242Klinhas. Não tenho NOT NULLrestrições na coluna, mas na verdade elas são NOT NULL.

Agora, estou medindo o desempenho das consultas:

EU

SELECT plat_id, p_id, pl_id, player_account player_account
FROM(
    SELECT plat_id, p_id, pl_id, 
    COALESCE(amount, 0) player_account,
    ROW_NUMBER() OVER (PARTITION BY plat_id, p_id, pl_id, currency_id
                       ORDER BY date DESC NULLS LAST) rn
    FROM dpa
) sub WHERE rn = 1;

Plano analisado:

Subquery Scan on sub  (cost=0.42..25484.16 rows=1214 width=44) (actual time=0.044..296.810 rows=215274 loops=1)
  Filter: (sub.rn = 1)
  Rows Removed by Filter: 27556
  ->  WindowAgg  (cost=0.42..22448.79 rows=242830 width=28) (actual time=0.043..255.690 rows=242830 loops=1)
        ->  Index Only Scan using idx1 on dpa  (cost=0.42..16378.04 rows=242830 width=28) (actual time=0.037..91.576 rows=242830 loops=1)"
              Heap Fetches: 242830

II

SELECT DISTINCT ON(plat_id, p_id, pl_id, currency_id)
       plat_id, p_id, pl_id, currency_id, amount
FROM dpa
ORDER BY plat_id, p_id, pl_id, currency_id, date DESC NULLS LAST

Plano analisado:

Unique  (cost=0.42..18794.73 rows=82273 width=28) (actual time=0.017..128.277 rows=215274 loops=1)
  ->  Index Only Scan using idx1 on dpa  (cost=0.42..16366.43 rows=242830 width=28) (actual time=0.016..72.110 rows=242830 loops=1)
        Heap Fetches: 242830

Como pode ser visto, a segunda consulta é mais rápida que a primeira. Mas quando executo essas consultas PGAdmin, obtive as seguintes estatísticas médias:

A consulta com ROW_NUMBER()(o primeiro):4999 ms

A consulta com DISTINCT ON(o segundo):5654 ms

Entendo que bandwith/ latencya sobrecarga em um conjunto de resultados tão grande é significativa. Todas as consultas produzem 215274linhas.

PERGUNTA: Por que demora mais tempo para receber todas as linhas no segundo caso do que no primeiro, embora o planejador mostre que o segundo plano é mais ideal?

Eu tenho esta tabela no PostgreSQL 9.4:

CREATE TABLE user_operations( 
    id SERIAL PRIMARY KEY, 
    operation_id integer, 
    user_id integer )

A tabela consiste em ~1000-2000diferentes operações, cada uma correspondendo a algum subconjunto (consistindo aproximadamente de 80000-120000elementos cada) do conjunto Sde todos os usuários:

S = {1, 2, 3, ... , 122655}

Parâmetros:

work_mem = 128MB
table_size = 880MB

Eu também tenho um índice no arquivo operation_id.

PERGUNTA: Qual seria o plano ideal para consultar todos os distintosuser_idpara uma parte significativa dooperation_idconjunto (20%-60%) como:

SELECT DISTINCT user_id FROM user_operation WHERE operation_id < 500

É possível criar mais índices na tabela . Atualmente, o plano para a consulta é:

HashAggregate  (cost=196173.56..196347.14 rows=17358 width=4) (actual time=1227.408..1359.947 rows=598336 loops=1)
  ->  Bitmap Heap Scan on user_operation  (cost=46392.24..189978.17 rows=2478155 width=4) (actual time=233.163..611.182 rows=2518122 loops=1)
        Recheck Cond: (operation_id < 500)
        ->  Bitmap Index Scan on idx  (cost=0.00..45772.70 rows=2478155 width=0) (actual time=230.432..230.432 rows=2518122 loops=1)
              Index Cond: (operation_id < 500)

Esse plano de consulta é realmente ideal nessas circunstâncias? Quero dizer, não tenho certeza sobre a correção de usar Bitmap Heap Scan. Eu apreciarei todas as referências a artigos relevantes.

Estou tentando entender o FILLFACTORparâmetro index em um exemplo simples. Tenho uma tabela que geralmente é atualizada apenas uma vez a cada dez dias, mas a atualização é bastante pesada (requer de 5 a 6 horas).

Agora, costumo soltar o índice em uma coluna da tabela, atualizar os dados e criar o índice novamente com FILLFACTOR = 100.

Isso é uma boa prática? Não tenho certeza disso porque não entendo o FILLFACTORconceito em geral. Por padrão é 90% e dizem que é necessário para fazer atualizações e inserções.

Mas tentei criar um índice com FILLFACTOR = 100e, em seguida, atualizar algumas linhas e adicionar uma nova e funcionou bem. Mas eu autovacuumvirei off. Correr ANALYZE, no entanto, exigia apenas 3 segundos.

Então, como funciona o espaço livre deixado pelo fillfactor?