Hassan Syed's questions

Estou trabalhando em um gateway de armazenamento multilocatário.

Cada inquilino tem seu próprio esquema -- o esquema para cada inquilino é um meta-esquema, então cada esquema de inquilino é idêntico. As interações do banco de dados ocorrem apenas por meio de uma API de procedimento armazenado. A camada de procedimento armazenado está no esquema público. Os procedimentos armazenados dependem do search_path sendo definido como <TENANT_SCHEMA>;public.

A camada de aplicativo prepara instruções em relação aos procedimentos armazenados no esquema público.

Eu tenho um procedimento armazenado para alternar o locatário que se parece com o seguinte:

CREATE OR REPLACE FUNCTION domains_switch(domain DOMAIN_NAME) RETURNS VOID AS $$ BEGIN
  IF EXISTS (SELECT NULL FROM public.domains WHERE domain_name = domain) THEN
    EXECUTE format('set search_path=%s,public;', domain);
  ELSE
    RAISE 'domain "%" does not exist',domain USING ERRCODE = 'AX001';
  END IF;
  RETURN; END;
$$ LANGUAGE plpgsql;

plpgsqlos procedimentos armazenados gerenciam os próprios planos, o que significa que só preciso preparar as instruções da API uma vez por back-end no gateway. Suspeito que os planos em cache internos do procedimento armazenado sejam invalidados quando alterno o esquema.

Estou planejando adicionar uma camada ao gerenciamento do pool de conexões do gateway para minimizar os switches search_path. Minha intuição está correta? Esta é a coisa certa a fazer?

Eu tenho uma consulta que está levando 40 segundos para ser executada nos 80ms que deveria se postgres onde filtrar a chave primária em vez de fazer uma varredura sequencial. Eu tenho que forçar o postgres a filtrar o índice definindo o sinalizador acima.

Ter que colocar isso no UDF parece um truque para mim, quero dizer, não é razoável IMO que o planejador de consulta esteja escolhendo uma varredura seq em vez de um filtro indexado. A maior parte da minha experiência com SQL está fora do SQL Server e nunca experimentei planejar mal essas otimizações básicas.

Editar: Para qualquer pessoa interessada, o tempo de execução da consulta caiu de 80 ms para 27 ms com 9,2, holly molley.

É possível ao usar o pgadminou plsqlobter um plano de consulta para uma instrução sql executada dentro de uma função definida pelo usuário ( UDF ) usando EXPLAIN. Então, como obtenho o plano de consulta para uma invocação específica de uma UDF? Vejo o UDF abstraído em uma única operação F()no pgadmin.

Já procurei na documentação mas não encontrei nada.

Atualmente, estou retirando as instruções e executando-as manualmente. Mas isso não vai cortá-lo para grandes consultas.

Por exemplo, considere o UDF abaixo. Essa UDF, embora tenha a capacidade de imprimir sua string de consulta, não funcionará com um copiar e colar, pois possui uma tabela temporária criada localmente, que não existe quando você a cola e executa.

CREATE OR REPLACE FUNCTION get_paginated_search_results(
    forum_id_ INTEGER,
    query_    CHARACTER VARYING,
    from_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
    to_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
    in_categories_ INTEGER[] DEFAULT '{}')
RETURNS SETOF post_result_entry AS $$
DECLARE
    join_string CHARACTER VARYING := ' ';
    from_where_date CHARACTER VARYING := ' ';
    to_where_date CHARACTER VARYING := ' ';
    query_string_ CHARACTER VARYING := ' ';
BEGIN
    IF NOT from_date_ IS NULL THEN
        from_where_date := ' AND fp.posted_at > ''' || from_date_ || '''';
    END IF;

    IF NOT to_date_ IS NULL THEN
        to_where_date := ' AND fp.posted_at < ''' || to_date_ || '''';
    END IF;

    CREATE LOCAL TEMP TABLE un_cat(id) ON COMMIT DROP AS (select * from unnest(in_categories_)) ;

    if in_categories_ != '{}' THEN
        join_string := ' INNER JOIN forum_topics ft ON fp.topic_id = ft.id ' ||
        ' INNER JOIN un_cat uc ON uc.id = ft.category_id ' ;
    END IF;

    query_string_ := '
    SELECT index,posted_at,post_text,name,join_date,quotes
    FROM forum_posts fp
    INNER JOIN forum_user fu ON
    fu.forum_id = fp.forum_id AND fu.id = fp.user_id' ||
        join_string
    ||
    'WHERE fu.forum_id = ' || forum_id_ || ' AND
    to_tsvector(''english'',fp.post_text) @@ to_tsquery(''english'','''|| query_||''')' || 
        from_where_date || 
        to_where_date
    ||';';

    RAISE NOTICE '%', query_string_ ;

    RETURN QUERY
    EXECUTE query_string_;
END;
$$ LANGUAGE plpgsql;