LaVache's questions

我需要获取有关所有 MATERIALIZED VIEW 列的元数据，包括它们的数据类型。我可以用这个查询来做到这一点：

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

但是它没有列出任何数组列，因为它们不存在于pg_catalog.pg_type表中（这是我从中获取数据类型的表）。

我如何也可以获得数组列的元数据（包括类型）？

我正在使用 postgres 13.3。

我正在使用 postgres 13.3，并且我已经有一个现有的表，其中有一GENERATED列，例如：

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)
);

但我现在改变了主意，我希望这只是一个常规的非生成列，我可以手动编辑......

• 但我想避免不得不DROP重新创建列
• 我想保留目前已经在列中的现有值

这可能吗？对列执行此操作的ALTER查询是什么the_column_to_alter？

我正在使用带有内部和外部查询的 Postgres 13.3，它们都只产生一行（只是一些关于行数的统计信息）。

我不明白为什么下面的 Query2 比 Query1 慢得多。它们基本上应该几乎完全相同，最多可能相差几毫秒......

查询1：需要49秒

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：耗时 4 分 30 秒

我只添加了一行：

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;

在此之前，并且在外部查询中有一些额外的列（应该几乎为零差异），整个查询非常慢，比如 25 分钟，我认为这可能是由于内联？因此OFFSET 0被添加到两个查询中（这确实有很大帮助）。

我也一直在使用上述 CTE 与子查询之间进行交换，但OFFSET 0包含它似乎没有任何区别。

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;

正如你所看到的，它是一个非常简单的函数，它只被调用一次，从整个事情产生的单行开始。这怎么会导致如此大规模的放缓？为什么它会影响 Postgres 是否内联初始子查询/CTE？（或者这里可能发生什么其他事情？）

此外，函数的作用根本不重要，只需将其替换为只返回一个TEXT字符串的函数即可hello导致初始内部查询的完全相同的减慢速度。所以这与函数“做什么”无关，而更像是某种“薛定谔的猫”效应，外部查询中的内容会影响内部查询的最初执行方式。为什么外部查询中的一个简单微小变化（对性能的影响基本上为零）会影响初始内部查询？

EXPLAIN ANALYZE输出：

• 查询1：https ://explain.depesz.com/s/bq7u
• 查询2：https ://explain.depesz.com/s/9w3rY