Stepan Parunashvili's questions

Contexto

Estou usando o postgres como um banco de dados gráfico multilocatário.

Todos os dados são armazenados em uma única tabela e eu confio em índices parciais para tornar as consultas eficientes.

Veja como é um esquema mínimo:

CREATE TABLE triples (
    app_id text NOT NULL,
    entity_id text NOT NULL,
    attr_id text NOT NULL,
    value jsonb NOT NULL,
    eav boolean NOT NULL DEFAULT false,
    ave boolean NOT NULL DEFAULT false,
    vae boolean NOT NULL DEFAULT false,
    created_at bigint NOT NULL DEFAULT 0,
    checked_data_type text
);

CREATE OR REPLACE FUNCTION triples_extract_number_value(value jsonb)
RETURNS double precision AS $$
BEGIN
  IF jsonb_typeof(value) = 'number' THEN
    RETURN value::double precision;
  ELSE
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE INDEX vae_idx ON triples (app_id, value, attr_id, entity_id) WHERE vae;

CREATE INDEX ave_number_idx ON triples (app_id, attr_id, triples_extract_number_value(value), entity_id) WHERE ave AND checked_data_type = 'number';

Em um aplicativo com conversations, groups, e messages, os dados podem ser armazenados como:

{app_id: 'app_1', entity_id: 'convo_1', attr_id: 'title': "My Conversation"} 
{app_id: 'app_1', entity_id: 'group_1', attr_id: 'title': "My Group"} 
{app_id: 'app_1', entity_id: 'convo_1', attr_id: 'group': "'group_1'", vae: true} // this 'links' convos to groups
{app_id: 'app_1', entity_id: 'msg_1', attr_id: 'time': "123", ave: true, checked_data_type: 'number'} // this indexes our 'time' field  
{app_id: 'app_1', entity_id: 'msg_1', attr_id: 'convo': "'convo_1'", vae: true} // this  links `msg_1` to `convo_1`

Meta

Quero responder à seguinte questão:

"Dê-me todos convosos que pertencem ao group'grupo 1' e que têm um messagewho's timemaior que 5."

Para fazer isso, escrevi a seguinte consulta, que faz 3 autojunções:

SELECT 
    DISTINCT(match_0_0.entity_id)
FROM 
    triples AS match_0_0
JOIN 
    triples AS match_0_1
    ON match_0_1.app_id = match_0_0.app_id
    AND match_0_1.vae = true
    AND match_0_1.attr_id = 'convo'
    AND match_0_1.value = to_jsonb(match_0_0.entity_id)
JOIN 
    triples AS match_0_2
    ON match_0_2.app_id = match_0_1.app_id
    AND match_0_2.ave = true
    AND match_0_2.attr_id = 'time'
    AND triples_extract_number_value(match_0_2.value) >= 5
    AND match_0_2.checked_data_type = 'number'
    AND match_0_2.entity_id = match_0_1.entity_id
WHERE 
    match_0_0.app_id = 'chat_app'
    AND match_0_0.vae = true
    AND match_0_0.attr_id = 'groups'
    AND match_0_0.value = '"group_1"';

Problema

O problema é que essa consulta leva cerca de 14 segundos para ser satisfeita. Executando com EXPLAIN (ANALYZE, BUFFERS), mostra:

Loop aninhado

Observando EXPLAIN (ANALYZE, BUFFERS), notei que a junção nested_loop tem muitos acertos de buffer.

Se eu tentar desabilitar junções de loop aninhadas, a consulta será resolvida em 40 ms :

SET enable_nestloop TO off;

SELECT 
    DISTINCT(match_0_0.entity_id)
FROM 
    triples AS match_0_0
JOIN 
    triples AS match_0_1
    ON match_0_1.app_id = match_0_0.app_id
    AND match_0_1.vae = true
    AND match_0_1.attr_id = 'convo'
    AND match_0_1.value = to_jsonb(match_0_0.entity_id)
JOIN 
    triples AS match_0_2
    ON match_0_2.app_id = match_0_1.app_id
    AND match_0_2.ave = true
    AND match_0_2.attr_id = 'time'
    AND triples_extract_number_value(match_0_2.value) >= 5
    AND match_0_2.checked_data_type = 'number'
    AND match_0_2.entity_id = match_0_1.entity_id
WHERE 
    match_0_0.app_id = 'chat_app'
    AND match_0_0.vae = true
    AND match_0_0.attr_id = 'groups'
    AND match_0_0.value = '"group_1"';

Pergunta

Existe alguma maneira de dar uma dica ao postgres para que ele escolha uma estratégia melhor?

Reprodução

Configurei uma reprodução no DB Fiddle, que mostra a consulta lenta:

https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/15620