James Hay's questions

Na minha API, o usuário pode enviar uma solicitação que tenta criar uma nova linha quando existe uma linha com essa chave exclusiva.

Atualmente, estou detectando o erro de chave exclusiva e retornando uma mensagem informando que X já existe. Mas é mais eficiente pesquisar a linha primeiro (na mesma conexão) e executar a instrução INSERT apenas se essa linha não existir?

Minha intuição diz que ler o erro do Postgres deveria ser mais eficiente, mas gostaria de ter certeza de que estou fazendo as coisas de maneira idiomática.

Postgres é a versão 12

Tenho duas tabelas no Postgres 9.4.1 eventse event_refscom os seguintes schemas:

eventstabela

CREATE TABLE events (
  id serial NOT NULL PRIMARY KEY,
  event_type text NOT NULL,
  event_path jsonb,
  event_data jsonb,
  created_at timestamp with time zone NOT NULL
);

-- Index on type and created time

CREATE INDEX events_event_type_created_at_idx
  ON events (event_type, created_at);

event_refstabela

CREATE TABLE event_refs (
  event_id integer NOT NULL,
  reference_key text NOT NULL,
  reference_value text NOT NULL,
  CONSTRAINT event_refs_pkey PRIMARY KEY (event_id, reference_key, reference_value),
  CONSTRAINT event_refs_event_id_fkey FOREIGN KEY (event_id)
      REFERENCES events (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Ambas as tabelas contêm 2 milhões de linhas. Esta é a consulta que estou executando

SELECT
  EXTRACT(EPOCH FROM (MAX(events.created_at) - MIN(events.created_at))) as funnel_time
FROM
  events
INNER JOIN
  event_refs
ON
  event_refs.event_id = events.id AND
  event_refs.reference_key = 'project'
WHERE
    events.event_type = 'event1' OR
    events.event_type = 'event2' AND
    events.created_at >= '2015-07-01 00:00:00+08:00' AND
    events.created_at < '2015-12-01 00:00:00+08:00'
GROUP BY event_refs.reference_value
HAVING COUNT(*) > 1

Estou ciente da precedência do operador na cláusula where. Ele deve apenas filtrar eventos com o tipo 'event2' por data.

Esta é a EXPLAIN ANALYZEsaída

GroupAggregate  (cost=116503.86..120940.20 rows=147878 width=14) (actual time=3970.530..4163.041 rows=53532 loops=1)
   Group Key: event_refs.reference_value
   Filter: (count(*) > 1)
   Rows Removed by Filter: 41315
   ->  Sort  (cost=116503.86..116873.56 rows=147878 width=14) (actual time=3970.509..4105.316 rows=153766 loops=1)
         Sort Key: event_refs.reference_value
         Sort Method: external merge  Disk: 3904kB
         ->  Hash Join  (cost=24302.26..101275.04 rows=147878 width=14) (actual time=101.667..1394.281 rows=153766 loops=1)
               Hash Cond: (event_refs.event_id = events.id)
               ->  Seq Scan on event_refs  (cost=0.00..37739.00 rows=2000000 width=10) (actual time=0.007..368.661 rows=2000000 loops=1)
                     Filter: (reference_key = 'project'::text)
               ->  Hash  (cost=21730.79..21730.79 rows=147878 width=12) (actual time=101.524..101.524 rows=153766 loops=1)
                     Buckets: 16384  Batches: 2  Memory Usage: 3315kB
                     ->  Bitmap Heap Scan on events  (cost=3761.23..21730.79 rows=147878 width=12) (actual time=23.139..75.814 rows=153766 loops=1)
                           Recheck Cond: ((event_type = 'event1'::text) OR ((event_type = 'event2'::text) AND (created_at >= '2015-07-01 04:00:00+12'::timestamp with time zone) AND (created_at < '2015-12-01 05:00:00+13'::timestamp with time zone)))
                           Heap Blocks: exact=14911
                           ->  BitmapOr  (cost=3761.23..3761.23 rows=150328 width=0) (actual time=21.210..21.210 rows=0 loops=1)
                                 ->  Bitmap Index Scan on events_event_type_created_at_idx  (cost=0.00..2349.42 rows=102533 width=0) (actual time=12.234..12.234 rows=99864 loops=1)
                                       Index Cond: (event_type = 'event1'::text)
                                 ->  Bitmap Index Scan on events_event_type_created_at_idx  (cost=0.00..1337.87 rows=47795 width=0) (actual time=8.975..8.975 rows=53902 loops=1)
                                       Index Cond: ((event_type = 'event2'::text) AND (created_at >= '2015-07-01 04:00:00+12'::timestamp with time zone) AND (created_at < '2015-12-01 05:00:00+13'::timestamp with time zone))
 Planning time: 0.493 ms
 Execution time: 4178.517 ms

Estou ciente de que o filtro na event_refsvarredura da tabela não está filtrando nada, isso é resultado dos meus dados de teste, haverá tipos diferentes adicionados posteriormente.

Tudo abaixo e incluindo o HashJoinparece razoável fornecer meus dados de teste, mas estou me perguntando se é possível aumentar a Sortvelocidade da GROUP BYcláusula?

Eu tentei adicionar um índice de b-tree à reference_valuecoluna, mas não parece usá-lo. Se não estou enganado (e posso muito bem estar, por favor me diga), está classificando 153.766 linhas. Um índice não seria benéfico para esse processo de classificação?

Eu tenho uma tabela de cerca de 3,25 milhões de linhas com o seguinte formato no Postgres 9.4.1

CREATE TABLE stats
(
    id serial NOT NULL,
    type character varying(255) NOT NULL,
    "references" jsonb NOT NULL,
    path jsonb,
    data jsonb,
    "createdAt" timestamp with time zone NOT NULL,
    CONSTRAINT stats_pkey PRIMARY KEY (id)
)
WITH (
    OIDS=FALSE
);

O typeé uma string simples com no máximo 50 caracteres.

A referencescoluna é um objeto com uma lista de valores-chave. Basicamente, qualquer lista de valores de chave simples e apenas 1 nível de profundidade, os valores são sempre strings. Poderia ser

{
    "fruit": "plum"
    "car": "toyota"
}

ou pode ser

{
    "project": "2532"
}

O createdAttimestamp nem sempre é gerado a partir do banco de dados (mas será por padrão se um valor não for fornecido)

No momento, estou usando a tabela apenas com dados de teste. Nesses dados, cada linha tem uma projectchave como referência. Portanto, há 3,25 milhões de linhas com uma chave de projeto. Existem exatamente 400.000 valores distintos para a projectreferência. Existem apenas 5 valores distintos para o typecampo, isso provavelmente não passaria de algumas centenas em produção.

Então estou tentando indexar a tabela para realizar a seguinte query rapidamente:

SELECT
  EXTRACT(EPOCH FROM (MAX("createdAt") - MIN("createdAt"))) 
FROM
  stats
WHERE
  stats."references"::jsonb ? 'project' AND
  (
    stats."type" = 'event1' OR
    (
      stats."type" = 'event2' AND
      stats."createdAt" > '2015-11-02T00:00:00+08:00' AND
      stats."createdAt" < '2015-12-03T23:59:59+08:00'
    )
  )
GROUP BY stats."references"::jsonb->> 'project'

A consulta retorna a distância de tempo entre dois eventos com base em duas linhas de estatísticas que têm a mesma referência. Neste caso project. Sempre há apenas 1 linha para cada valor typeselecionado reference, mas também pode não haver linhas, caso em que o resultado retornado é 0 (isso é calculado posteriormente em uma parte diferente de uma consulta maior).

Eu criei um índice nas colunas createdAt typee references, mas o plano de execução da consulta parece estar fazendo uma verificação completa.

O índice

CREATE INDEX "stats_createdAt_references_type_idx"
    ON stats
    USING btree
    ("createdAt", "references", type COLLATE pg_catalog."default");

Plano de execução:

 HashAggregate  (cost=111188.31..111188.33 rows=1 width=38) 
                (actual time=714.499..714.499 rows=0 loops=1)
   Group Key: ("references" ->> 'project'::text)
      ->  Seq Scan on stats  (cost=0.00..111188.30 rows=1 width=38) 
                             (actual time=714.498..714.498 rows=0 loops=1)
          Filter: (
              (("references" ? 'project'::text) 
               AND ((type)::text = 'event1'::text)) OR 
              (((type)::text = 'event2'::text) 
               AND ("createdAt" > '2015-11-02 05:00:00+13'::timestamp with time zone) 
               AND ("createdAt" < '2015-12-04 04:59:59+13'::timestamp with time zone)))

Rows Removed by Filter: 3258680
Planning time: 0.163 ms
Execution time: 714.534 ms

Eu realmente não estou tão informado sobre planos de execução de indexação e consulta, então se alguém pudesse me indicar a direção certa, seria ótimo.

Editar

Conforme observado por Erwin, parece que mesmo se eu tivesse os índices corretos, uma verificação da tabela ainda ocorreria, pois a parte da tabela retornada da consulta é muito grande. Isso significa que, para esse conjunto de dados, esse é o tempo de consulta mais rápido que posso obter? Estou assumindo que se eu adicionasse mais 60 milhões de linhas não relacionadas sem uma referência de projeto, ele poderia usar um índice (se eu tivesse os índices corretos), mas não vejo como isso poderia acelerar a consulta adicionando mais dados. Talvez eu esteja perdendo alguma coisa.