LaVache's questions

Eu preciso obter metadados sobre todas as colunas MATERIALIZED VIEW, incluindo seu tipo de dados. Eu posso fazer isso com esta consulta:

SELECT 

trim(leading '_' from pg_type.typname) as data_type,
pg_namespace.nspname as schema_name, 
pg_namespace.nspname as udt_name, 
attnum AS ordinal_position,
pg_attribute.attname as column_name,
pg_class.relname as matview_name, 
'-------' AS spacer,
*


from pg_catalog.pg_attribute
join pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid
join pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace
join pg_catalog.pg_type ON pg_type.typelem = pg_attribute.atttypid
where 
    pg_class.relkind = 'm' AND
    not pg_attribute.attisdropped and 
    cast(pg_type.typanalyze as text) = 'array_typanalyze' and 
    pg_attribute.attnum > 0
order by 
    pg_attribute.attnum

No entanto, ele não lista nenhuma coluna de matriz, devido a elas não existirem na pg_catalog.pg_typetabela (que é a tabela da qual estou obtendo os tipos de dados).

Como também posso obter metadados (incluindo tipo) para colunas de matriz também?

Estou usando o postgres 13.3.

Estou usando o postgres 13.3 e já tenho uma tabela existente com uma GENERATEDcoluna como:

CREATE TABLE test_table (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    the_column_to_alter bool NOT NULL GENERATED ALWAYS AS (2 > 1) STORED,
    CONSTRAINT test_table2_pkey PRIMARY KEY (id)
);

Mas agora mudei de ideia e quero que isso seja apenas uma coluna regular não gerada que eu possa editar manualmente ...

• Mas eu quero evitar ter que DROPrecriar a coluna
• E quero manter os valores existentes que já estão na coluna no momento

Isso é possível? Qual seria a ALTERconsulta para fazer isso para a the_column_to_altercoluna?

Estou usando o Postgres 13.3 com consultas internas e externas que produzem apenas uma única linha (apenas algumas estatísticas sobre contagens de linhas).

Não consigo descobrir por que o Query2 abaixo é muito mais lento que o Query1. Eles devem basicamente ser quase exatamente os mesmos, talvez alguns ms de diferença no máximo ...

Consulta1: leva 49 segundos

WITH t1 AS (
        SELECT
            (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,
            (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,
            (SELECT COUNT(*) FROM racing.xday) AS xday_row_count
        OFFSET 0 -- this is to prevent inlining
)

SELECT
            t1.all_count,
            t1.all_count-t1.todo_count AS done_count,
            t1.todo_count,
            t1.xday_row_count
FROM t1;

Query2: leva 4 minutos e 30 segundos

E eu adicionei apenas uma linha:

WITH t1 AS (
        SELECT
            (SELECT COUNT(*) FROM racing.all_computable_xformula_bday_combos) AS all_count,
            (SELECT COUNT(*) FROM racing.xday_todo_all) AS todo_count,
            (SELECT COUNT(*) FROM racing.xday) AS xday_row_count
        OFFSET 0 -- this is to prevent inlining
)

SELECT
            t1.all_count,
            t1.all_count-t1.todo_count AS done_count,
            t1.todo_count,
            t1.xday_row_count,
            -- the line below is the only difference to Query1:
            util.divide_ints_and_get_percentage_string(todo_count, all_count) AS todo_percentage
FROM t1;

Antes desse ponto, e com algumas colunas extras na consulta externa (o que deveria ter feito quase nenhuma diferença), a consulta inteira era incrivelmente lenta, como 25 minutos, o que acho que foi devido ao inlining talvez? Daí a OFFSET 0adição em ambas as consultas (o que ajuda muito).

Eu também tenho trocado entre usar os CTEs acima vs subconsultas, mas com o OFFSET 0incluído não parece fazer nenhuma diferença.

Definições das funções que estão sendo chamadas em Query2:

CREATE OR REPLACE FUNCTION util.ratio_to_percentage_string(FLOAT, INTEGER) RETURNS TEXT AS $$ BEGIN
    RETURN ROUND($1::NUMERIC * 100, $2)::TEXT || '%';
END; $$ LANGUAGE plpgsql IMMUTABLE;


CREATE OR REPLACE FUNCTION util.divide_ints_and_get_percentage_string(BIGINT, BIGINT) RETURNS TEXT AS $$ BEGIN
    
    RETURN CASE 
        WHEN $2 > 0 THEN util.ratio_to_percentage_string($1::FLOAT / $2::FLOAT, 2)
        ELSE 'divide_by_zero' 
        END
        ;

END; $$ LANGUAGE plpgsql IMMUTABLE;

Como você pode ver, é uma função muito simples, que está sendo chamada apenas uma vez, a partir de uma única linha que a coisa toda produz. Como isso pode causar uma desaceleração tão grande? E por que está afetando se o Postgres inline a subconsulta inicial / CTE? (Ou o que mais pode estar acontecendo aqui?)

Além disso, não importa o que a função faz, simplesmente substituí-la por uma função que não faz nada além de retornar uma TEXTstring hellocausa exatamente a mesma lentidão da consulta interna inicial. Portanto, não se trata de algo que a função "faça", mas mais como algum tipo de efeito "gato de Schrödinger", onde coisas na consulta externa estão afetando como a consulta interna é executada inicialmente. Por que uma pequena mudança simples na consulta externa (que basicamente tem efeito zero no desempenho) afeta a consulta interna inicial?

EXPLAIN ANALYZEsaídas:

• Consulta1: https://explain.depesz.com/s/bq7u
• Consulta2: https://explain.depesz.com/s/9w3rY