Justin Lowen's questions

Estou executando o PG10 (em breve para mudar para o PG13) e estou tendo problemas de desempenho com uma consulta específica. Também testei no PG13 e obtive resultados semelhantes. A maioria das junções nesta consulta maior são selecionadas para Nested Joins, mas parece que um Hash Join seria mais apropriado para a maioria.

Vejo que os primeiros nós unidos podem estar direcionando as estatísticas de junções posteriores, onde as estimativas de linha são bem baixas. As primeiras junções são as seguintes: https://explain.dalibo.com/plan/b5045eb7cffdf0dh

SELECT * from workflowable_resource where sample_type_id in (SELECT resource_id from resource where name = 'Test Order')

Embora essa consulta menor seja bem rápida, vejo muitos exemplos em que Nested Loop é selecionado incorretamente, onde um Hash Join pode ter um desempenho melhor. (A estimativa de linha é 1, ou próximo de 1, mas as linhas reais são ordens de magnitude maiores)

Quais são algumas técnicas gerais para melhorar estimativas de linha nesses casos? Tentei aumentar default_statistics_target para 10000 e analisar todas as tabelas novamente, mas não vi nenhuma melhoria nas estimativas de linha.

Na consulta maior, no banco de dados ampliado, há um nó tomando todo o tempo. Há uma varredura de índice na tabela, mas ela é realizada em um loop aninhado, pois o lado esquerdo é estimado em 1 linha, mas neste ponto está sendo executado com 178.000 linhas. Parece que erros de estimativas de linhas anteriores estão sendo levados adiante.

Estou executando o PostgreSQL 13.15 em um contêiner docker local, com versões pg_dump/pg_restore correspondentes. Ao despejar e restaurar o banco de dados, uma das funções personalizadas que estava presente anteriormente parece estar faltando quando se trata de criar a tabela. A tabela de interesse é ignorada durante seu COPYcomando. Após a restauração do banco de dados, a mesma função com a mesma entrada pode ser executada com sucesso sem problemas.

Reduzi o pg_dump de texto simples para o exemplo reproduzível mínimo. Aqui está o erro que ocorre durante a restauração:

CREATE TABLE
ALTER TABLE
COMMENT
WARNING:  Value: Equipment
WARNING:  TYPE: text
ERROR:  function try_cast_jsonb(text) does not exist
LINE 1: SELECT try_cast_jsonb(raw_text)
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT try_cast_jsonb(raw_text)
CONTEXT:  PL/pgSQL function public.content_aware_tsvector(regconfig,text[],character) line 18 at assignment
COPY searchable_items, line 1: "84      name    {}      {}      {Equipment}     A       app_user"

Aqui está a execução da função após a conclusão da restauração no shell psql:

mydb=# SELECT try_cast_jsonb('Equipment');
 try_cast_jsonb 
----------------

A função retorna nulo se a string não puder ser convertida em jsonb.

O dump do banco de dados foi executado com o seguinte comando:

pg_dump --host localhost --port 1487 --dbname mydb --clean --if-exists > backuptest.sql

A restauração foi realizada da seguinte forma em um banco de dados limpo:

pg_restore --host localhost --port 1487 --dbname mydb --clean --if-exists --no-owner backuptest.sql

Isso ocorre tanto no formato simples quanto no formato C.

Aqui está o pg_dump em texto simples:

--
-- PostgreSQL database dump
--

-- Dumped from database version 13.15 (Debian 13.15-1.pgdg120+1)
-- Dumped by pg_dump version 13.15 (Debian 13.15-1.pgdg120+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

-- DROP ALL CONSTRAINTS, INDEXES, FUNCTIONS, EXTENSIONS if existing
DROP FUNCTION IF EXISTS public.try_cast_jsonb(_text text);

DROP FUNCTION IF EXISTS public.content_aware_tsvector(_lang text, _raw_text text, _tsweight character);
DROP FUNCTION IF EXISTS public.content_aware_tsvector(_lang regconfig, _raw_text_arr text[], _tsweight character);
DROP EXTENSION IF EXISTS pg_trgm;
DROP EXTENSION IF EXISTS btree_gist;
--
-- Name: btree_gist; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA public;


--
-- Name: EXTENSION btree_gist; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION btree_gist IS 'support for indexing common datatypes in GiST';


--
-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;


--
-- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';


--
-- Name: try_cast_jsonb(text); Type: FUNCTION; Schema: public; Owner: app_user
--

CREATE FUNCTION public.try_cast_jsonb(_text text) RETURNS jsonb
    LANGUAGE plpgsql STABLE
    AS $$
        DECLARE
            trimmed_text text;
            casted_json jsonb;
        BEGIN
            -- Remove leading whitespace
            trimmed_text := ltrim(_text);

            -- Check if the string doesn't start with '{' or '['
            IF NOT (left(trimmed_text, 1) = '{' OR left(trimmed_text, 1) = '[') THEN
                RETURN NULL;
            END IF;

            BEGIN
                SELECT trimmed_text::jsonb INTO casted_json;
            EXCEPTION WHEN OTHERS THEN
                RETURN NULL;
        END;

        RETURN casted_json;
        END;
    $$;


ALTER FUNCTION public.try_cast_jsonb(_text text) OWNER TO app_user;


--
-- Name: content_aware_tsvector(regconfig, text[], character); Type: FUNCTION; Schema: public; Owner: app_user
--

CREATE FUNCTION public.content_aware_tsvector(_lang regconfig, _raw_text_arr text[], _tsweight character DEFAULT NULL::bpchar) RETURNS tsvector
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
        DECLARE
            vec tsvector;
            i integer;
            raw_text text;
            json_text jsonb;
            debug text;
        BEGIN
            vec := '';

            FOR i IN array_lower(_raw_text_arr, 1) .. array_upper(_raw_text_arr, 1) LOOP

                raw_text := COALESCE(_raw_text_arr[i], '');

                RAISE WARNING 'Value: %', raw_text;
                SELECT pg_typeof(raw_text) INTO debug; 
                RAISE WARNING 'TYPE: %', debug;
                json_text := try_cast_jsonb(raw_text);

                IF json_text IS NOT NULL THEN
                    -- JSON aware tsvector only indexes contents and not structure.
                    vec := vec || jsonb_to_tsvector(_lang, json_text, '["string", "numeric", "boolean"]'::jsonb);
                ELSEIF raw_text ~ '^[a-fA-F0-9]{8}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{12}$' THEN
                    -- Makes UUIDs a single element in the index for only exact matches.
                    vec := vec || array_to_tsvector(ARRAY[raw_text]);
                ELSE
                    vec := vec || to_tsvector(_lang, raw_text);
                END IF;

            END LOOP;

            IF _tsweight IS NOT NULL THEN
                RETURN setweight(vec, _tsweight::"char");
            END IF;

            RETURN vec;
        END;
    $_$;


ALTER FUNCTION public.content_aware_tsvector(_lang regconfig, _raw_text_arr text[], _tsweight character) OWNER TO app_user;

--
-- Name: content_aware_tsvector(text, text, character); Type: FUNCTION; Schema: public; Owner: app_user
--

CREATE FUNCTION public.content_aware_tsvector(_lang text, _raw_text text, _tsweight character DEFAULT NULL::bpchar) RETURNS tsvector
    LANGUAGE plpgsql IMMUTABLE
    AS $$
            DECLARE
                raw_json json;
                vec tsvector;
            BEGIN
                BEGIN
                    raw_json = _raw_text::json;
                EXCEPTION
                    WHEN others THEN
                        raw_json = NULL;
                END;

                vec = (
                    CASE
                        WHEN raw_json IS NOT NULL THEN json_to_tsvector(_lang::regconfig, raw_json, '["string", "numeric", "boolean"]')
                        ELSE to_tsvector(_lang::regconfig, _raw_text)
                    END
                );

                IF _tsweight IS NOT NULL THEN
                    RETURN setweight(vec, _tsweight::"char");
                ELSE
                    RETURN vec;
                END IF;

            END;
        $$;


ALTER FUNCTION public.content_aware_tsvector(_lang text, _raw_text text, _tsweight character) OWNER TO app_user;



SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: searchable_items; Type: TABLE; Schema: public; Owner: app_user
--

CREATE TABLE public.searchable_items (
    item_id integer NOT NULL,
    item_type text NOT NULL,
    pk_meta jsonb DEFAULT '{}'::jsonb NOT NULL,
    meta jsonb DEFAULT '{}'::jsonb NOT NULL,
    raw_text text[] NOT NULL,
    tsweight character(1) DEFAULT NULL::bpchar,
    tenant character varying DEFAULT CURRENT_USER NOT NULL,
    var_id integer GENERATED ALWAYS AS ((((meta || pk_meta) ->> 'resource_var_id'::text))::integer) STORED,
    val_id integer GENERATED ALWAYS AS ((((meta || pk_meta) ->> 'resource_val_id'::text))::integer) STORED,
    search_tsv_english tsvector GENERATED ALWAYS AS (public.content_aware_tsvector('english'::regconfig, raw_text, tsweight)) STORED
);


ALTER TABLE public.searchable_items OWNER TO app_user;

--
-- Name: TABLE searchable_items; Type: COMMENT; Schema: public; Owner: app_user
--

COMMENT ON TABLE public.searchable_items IS 'Table for optimizing text based search';

--
-- Data for Name: searchable_items; Type: TABLE DATA; Schema: public; Owner: app_user
--

COPY public.searchable_items (item_id, item_type, pk_meta, meta, raw_text, tsweight, tenant) FROM stdin;
84  name    {}  {}  {Equipment} A   app_user
84  description {}  {}  {"core resource class for the scheduling of equipment"} C   app_user


--
-- Name: searchable_items searchable_items_pkey; Type: CONSTRAINT; Schema: public; Owner: app_user
--

ALTER TABLE ONLY public.searchable_items
    ADD CONSTRAINT searchable_items_pkey PRIMARY KEY (item_id, item_type, pk_meta);


--
-- Name: trgm_search_idx; Type: INDEX; Schema: public; Owner: app_user
--

CREATE INDEX trgm_search_idx ON public.searchable_items USING gin (((array_to_tsvector(array_remove(raw_text, NULL::text)))::text) public.gin_trgm_ops);



--
-- PostgreSQL database dump complete
--

O que estou esquecendo? O que pode fazer com que um pg_dump falhe quando restaurado nas mesmas versões de ferramentas PostgreSQL e psql?

A tabela que não está sendo restaurada é a seguinte (também incorporada no dump acima):

CREATE TABLE public.searchable_items (
    item_id integer NOT NULL,
    item_type text NOT NULL,
    pk_meta jsonb DEFAULT '{}'::jsonb NOT NULL,
    meta jsonb DEFAULT '{}'::jsonb NOT NULL,
    raw_text text[] NOT NULL,
    tsweight character(1) DEFAULT NULL::bpchar,
    tenant character varying DEFAULT CURRENT_USER NOT NULL,
    var_id integer GENERATED ALWAYS AS ((((meta || pk_meta) ->> 'resource_var_id'::text))::integer) STORED,
    val_id integer GENERATED ALWAYS AS ((((meta || pk_meta) ->> 'resource_val_id'::text))::integer) STORED,
    search_tsv_english tsvector GENERATED ALWAYS AS (public.content_aware_tsvector('english'::regconfig, raw_text, tsweight)) STORED
);

A search_tsv_englishcoluna calculada que é derivada das colunas raw_texte tsweightcausa a falha. Esta função personalizada deve ser definida antes que o COPYcomando para restaurar a tabela seja executado, mas o erro relata que a função não está presente ou que há uma incompatibilidade de tipo de argumento.

Executar o COPYcomando manualmente após a restauração funciona sem problemas, como se a função não tivesse sido persistida antes do COPYcomando.

Existe alguma maneira de minimizar o impacto da latência entre o aplicativo e o banco de dados ao executar o PostgreSQL e SQLAlchemy ORM? Alterar estratégias de carregamento ou propriedades de pré-carregamento tem sido eficaz - apenas reduzindo geralmente o número de instruções entre aplicativo e banco de dados. Existem outras técnicas que podem ser usadas para minimizar o impacto dessa sobrecarga de conexão?

Nos gráficos Grafana abaixo, simulei uma latência de 10ms entre aplicação e banco de dados usando o tcmódulo de controle de tráfego. No meio do caminho, removi essa latência artificial, resultando em latência de cerca de 300 microssegundos ao cronometrar SELECT 1;entre o aplicativo e o banco de dados (interface de loopback localhost - banco de dados dentro do mesmo contêiner do aplicativo).

Isso parece indicar sensibilidade à latência nos endpoints medidos do processo/API.

Eu tenho um sistema que usa o formato UUID7 proposto para seus valores uuid. UUID7 tem carimbo de data/hora codificado em seu valor. O PostgreSQL documenta como as cláusulas ORDER BY funcionam com o tipo de dados UUID?

https://www.postgresql.org/docs/current/datatype-uuid.html Não há notas sobre classificação aqui.

lab7=# SELECT resource_id, uuid, created_timestamp from resource where "name" in ('ABC000004',
 'ABC000003') order by uuid, "name" desc;
 resource_id |                 uuid                 |     created_timestamp
      
-------------+--------------------------------------+----------------------
------
    17704701 | 018ec350-6976-79d5-b0f5-41af99b98fa4 | 2024-04-09 14:43:30.6
09909
    17704718 | 018ec353-ee50-7765-9e8b-3ebf22654662 | 2024-04-09 14:47:21.3
78086

Comparando os caracteres mais à esquerda (assumindo que se trata de uma string), esperaria que 018ec353 estivesse no conjunto de resultados antes de 018ec350. Como isso não é armazenado como uma string, essa suposição pode ser ingênua.

Em termos de desempenho, a classificação por resource_id aqui ou create_timestamp tem melhor desempenho, mas eu esperaria que a classificação no resource_id de incremento exclusivo, no uuid globalmente exclusivo e nocreate_timestamp estivessem todos alinhados. (A entrada criada mais recente é classificada primeiro quando ordenada por descrição)

Há algum detalhe sobre como essa classificação é realizada e por que minhas suposições estão erradas?

Existe uma prática recomendada para impor a ordem em que as condições de pesquisa são aplicadas? Vimos algumas consultas em que o otimizador reordenou as operações e passou linhas por meio de uma condição e/ou função de pesquisa inesperada e inválida.

Por exemplo:

SELECT * 
FROM table_1
join table_2 on table_1.primary_id = table_2.primary_id
join table_3 on table_2.secondary_id = table_3.secondary_id
WHERE table_3.value is json parseable and table_3.value->>'foo' = bar

Às vezes, essa consulta é otimizada de forma que a pesquisa JSON aconteça antes de garantir que o valor seja analisável em JSON.

Uma situação semelhante pode ocorrer durante a coerção dos tipos:

SELECT * 
FROM table_1
join table_2 on table_1.primary_id = table_2.primary_id and table_2.category_name = 'Foo'
join table_3 on table_2.secondary_id = table_3.secondary_id
-- first do regex to ensure we get a parseable int, then check range
-- column is stored as text as it can be mixed with other non-numerical inputs
WHERE table_3.value ~ E'^\\d{1,9}$' and table_3.value::int < :max_allowed_value_setting;

Parece que a condição de pesquisa de coerção de tipo pode ser reordenada antes da condição de pesquisa regex e/ou que os valores que devem ser filtrados table_2.category_name = 'Foo'são passados ​​primeiro pela conversão de número inteiro porque a ordem de junção foi alterada de table_1 -> table_2 -> table_3para table_1 -> table_3 -> table_2.

Sei que o otimizador de consulta pode reorganizar algumas coisas, mas ainda não tenho certeza de quando isso é aplicável e quando pode causar um problema. O otimizador não tem conhecimento das condições de pesquisa que dependem umas das outras, o que pode levar a erros durante a execução.

Quais partes de uma instrução SQL no PostgreSQL podem ser reorganizadas e existe uma prática recomendada (ou melhor) para lidar com esses tipos de cenários?

Editar: atualize a terminologia para usar condições de pesquisa em vez de restrições

Estou executando o PostgreSQL 13, e uma única instrução UPDATE que parece trivial para ser concluída pode levar horas (pelo menos em um caso, mais de 24 horas).

A seleção das linhas com uma instrução SELECT leva menos de um segundo. A atualização das linhas é drasticamente mais lenta, com apenas 30.000 linhas afetadas.

A coluna em questão que estou tentando atualizar não está indexada. O banco de dados também está bem provisionado para a escala do banco de dados. Enquanto a consulta está ativa, posso ver 1 dos 4 vCPU em plena aceleração. As configurações de memória são ajustadas aos padrões típicos recomendados para um sistema de 32 GB de RAM.

Após a primeira execução, posso alternar a coluna booleana arquivada para frente e para trás com muito mais rapidez (5 minutos na primeira ATUALIZAÇÃO -> 15 segundos para fazer o inverso no meu caso simulado).

Existem algumas propriedades que tornam esta tabela única. O esquema do banco de dados é altamente normalizado. Esta tabela contém os nomes e uuids de muitos dos modelos de dados, portanto, há muitos relacionamentos FK. A tabela possui 16 colunas, com 10 índices e 4 restrições. O valor da coluna atualizado não faz parte de uma restrição ou índice.

Por que o desempenho desta consulta de atualização é tão ruim? Há algo que eu possa fazer para melhorar o desempenho?

Atualmente, estou executando o PostgreSQL 13, mas também observei esse comportamento no PostgreSQL 11. Estou me deparando com um problema em que um erro de violação UniqueConstraint é gerado, mas esperaria bloquear pg_advisory_xact_lockaté que a transação fosse confirmada (e visível para outras transações aguardando na fechadura) ou revertido.

Estou calculando um valor exclusivo sort_idpara linhas em uma tabela com base em uma chave estrangeira na tabela de um aplicativo da web. UniqueConstraint(sort_id, foreign_key_value). Estamos utilizando pg_advisory_xact_lockem Python via SQL Alchemy e psycopg2. Originalmente, isso foi calculado com base em um modelo SQL Alchemy (veja o sort_id mais recente na propriedade do modelo). Também tentei uma consulta direta como ( SELECT max(sort_id) from table where foreign_key_value=:value;) e ainda encontrei erros de restrição.

À medida que a simultaneidade desta chamada/funcionalidade da API aumenta, a taxa de erros de restrição aumenta. Executando thread único, nunca encontramos o erro de restrição. Isso me leva a acreditar que o bloqueio pode ser liberado antes que os dados fiquem visíveis para outras transações.

Quando é pg_advisory_xact_locklançado? Os dados ficarão visíveis para outras transações antes que o bloqueio seja realizado por um processo diferente?

Ao executar o PostgreSQL 13.12 (ocorre em várias versões do PG11/PG13) usando o SQLAlchemy 1.3, ocasionalmente encontramos problemas em que o aumento da simultaneidade deixa certas transações (e suas transações aninhadas) no estado "Idle in Transaction" com a consulta RELEASE SAVEPOINT ....

Olhando para as consultas em execução atualmente, não está claro por que as transações pararam de avançar. Também observei esse comportamento sem travas suspensas:

Alguma maneira de depurar isso? Essa chamada de API geralmente funciona, com a sessão sendo limpa e não falha de forma consistente. Estamos usando o pool de conexões do SQL Alchemy para gerenciar as conexões - sessões fechadas retornarão a conexão ao pool e emitirão um rollback para limpar a conexão (quando o commit deveria ter confirmado todas as outras instruções).