EM0's questions

我正在尝试找出某个事务锁定了什么，从而阻止其他查询执行。我像这样查询了 pg_locks：

select c.relname, l.*
from pg_locks l
join pg_database d on l.database = d.oid
left join pg_class c on l.relation = c.oid
where d.datname = 'MYDB' and pid = MYPID

返回了一堆像这样的结果

relation对于大多数（并非全部）结果， 为 NULL。对于这些， classid和objid为非空（如上例所示）。我如何将它们解析为有用的对象标识符？根据pg_locks 文档，锁“由类 OID 和对象 OID 标识，方式与 pg_description 中相同”。但是，当我查询pg_description时，我找不到这些 oid。那里的 objoid 列的值都在 20000 以下 - 没有像 805681436 这样的值。

编辑：根据 Laurenz Albe 的回答更新查询（谢谢！）

select l.granted, l.locktype, l.mode, l.classid, l.objid, l.classid::regclass
    , c.relname, cns.conname -- relation
    , cns.conrelid::regclass as constraint_table, cns.contype -- constraint
    , trg.tgrelid::regclass as trigger_table, trg.tgname -- trigger
from pg_locks l
join pg_database d on l.database = d.oid
left join pg_class c on l.relation = c.oid
left join pg_constraint cns on l.objid = cns.oid and l.classid::regclass = 'pg_constraint'::regclass
left join pg_trigger trg on l.objid = trg.oid and l.classid::regclass = 'pg_trigger'::regclass
where d.datname = 'MYDB' and pid = MYPID

PostgreSQL 15.2。我有一个这样的分区表：

create table event
(
    dataset_id integer not null,
    event_id integer not null,
    second_offset integer not null,
    -- other columns
) partition by list (dataset_id);

该表的分区有 PKevent_id和 BRIN 索引second_offset：

create index event_DATASET_ID_ix_second_offset on event_DATASET_ID using brin (second_offset);

其中一些有数亿行。second_offset是事件发生的时间，并且行很快被插入，因此它紧密（但不完美）遵循行的物理顺序。这些表中的行永远不会更新或删除，只会插入和读取。

我运行这样的查询（简化）：

set enable_seqscan = off; 

select *
from event
where dataset_id = 365
    and second_offset <= timestamp_to_second_offset('2023-05-10') -- function that returns int
    and second_offset >= timestamp_to_second_offset('2023-05-09')

他们使用索引，但仍然很慢。解释分析显示：

Bitmap Heap Scan on event_365 event  (cost=453.13..2689210.37 rows=1322 width=54) (actual time=40651.983..40651.984 rows=0 loops=1)
  Recheck Cond: ((second_offset >= 405648000) AND (second_offset <= 405734399))
  Rows Removed by Index Recheck: 238676609
  Filter: (dataset_id = 365)
  Heap Blocks: lossy=1762985
  ->  Bitmap Index Scan on event_365_ix_second_offset  (cost=0.00..452.80 rows=52893390 width=0) (actual time=73.633..73.634 rows=17629850 loops=1)
        Index Cond: ((second_offset >= 405648000) AND (second_offset <= 405734399))
Planning Time: 0.673 ms
JIT:
  Functions: 6
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.672 ms, Inlining 4.802 ms, Optimization 19.712 ms, Emission 9.971 ms, Total 36.157 ms
Execution Time: 40653.748 ms

...直到我运行reindex index behavior.event_365_ix_second_offset，或者，vacuum behavior.event_366（我在两个不同的分区上尝试过）。然后查询变得非常快！EXPLAIN ANALYZE 然后显示：

Bitmap Heap Scan on event_365 event  (cost=596.29..5940945.52 rows=5967 width=54) (actual time=5.012..5.013 rows=0 loops=1)
  Recheck Cond: ((second_offset >= 405648000) AND (second_offset <= 405734399))
  Filter: (dataset_id = 365)
  ->  Bitmap Index Scan on event_365_ix_second_offset  (cost=0.00..594.80 rows=238696656 width=0) (actual time=5.008..5.008 rows=0 loops=1)
        Index Cond: ((second_offset >= 405648000) AND (second_offset <= 405734399))
Planning Time: 0.771 ms
JIT:
  Functions: 6
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.642 ms, Inlining 0.000 ms, Optimization 0.000 ms, Emission 0.000 ms, Total 1.642 ms
Execution Time: 6.940 ms

所以我的问题是如何确保查询始终快速运行。我应该添加一个 cron 作业来每晚重新索引它们吗？看起来有点老套，像 PG 这样的事情应该自动完成，不是吗？

pg_stat_user_tables显示 autovacuum 从未在大多数分区上运行 - 我现在不知道为什么。所以我不知道是否应该尝试强制 autovacuum 以某种方式运行。

类似于PostgreSQL 中 autovacuum_count、last_autovacuum 和 autoanalyze_count 的 NULL 和零的可能含义是什么？除了在我的情况下它是 PostgreSQL 15.1 并且没有删除 - 仅插入数据。

我有一个包含一些分区表和一些非分区表的数据库。有些partition有上亿行，但是n_live_tup对他们来说是0，vacuum_count,autovacuum_count,analyze_count,autoanalyze_count都是0。

只有最近创建的 2 个表（两个分区）分别具有 autoanalyze_count = 1 和 4，并且 n_live_tup > 0。但是，还有一些 n_live_tup > 0 的其他表（尽管没有对它们运行分析）。

        select relname, n_live_tup, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count
        from pg_catalog.pg_stat_user_tables
        where schemaname = 'myschema'

请注意，event_59 的 n_live_tup 在这里大错特错，该表实际上有 >11 亿行（这是最大的行）。

我尝试log_autovacuum_min_duration = 0按照链接问题中的建议进行设置，但到目前为止似乎没有记录任何内容。

为什么我的表没有得到分析？

编辑 1：现在连这些表的统计数据都被重置了：

服务器没有崩溃，因为 -pg_postmaster_start_time()返回2023-02-13 17:02:32.365095+00（那是我重新启动它的时候）。正如您在第一个屏幕截图中看到的那样，autoanalyze 很快就运行了。日志显示它在 20:10:18 再次运行，但尽管如此，所有分析/真空计数都为 0，并且 n_live_tup 已减少。

我手动运行analyze event_241并且 n_live_tup 变得准确。之后我重新启动了 PG，但没有重置计数，所以我不确定为什么它会在一夜之间重置。

编辑 2：当 pg_dump 备份运行时，服务器每晚都会崩溃，但显然没有重新启动 postmaster 进程，因为pg_postmaster_start_time()一直返回相同的值。我可以通过 pg_dumping 一个特定的数据库（不是这篇文章所讨论的数据库）轻松重现崩溃。

2023-02-17 09:04:16.607 UTC [27083] postgres@problematic_database LOG:  connection authorized: user=postgres database=problematic_database application_name=pg_dump
2023-02-17 09:04:16.609 UTC [27083] postgres@problematic_database PANIC:  could not open critical system index 2662
2023-02-17 09:04:16.610 UTC [11922] LOG:  server process (PID 27083) was terminated by signal 6: Aborted
2023-02-17 09:04:16.610 UTC [11922] LOG:  terminating any other active server processes
2023-02-17 09:04:16.645 UTC [11922] LOG:  all server processes terminated; reinitializing
2023-02-17 09:04:17.176 UTC [27945] LOG:  database system was interrupted; last known up at 2023-02-17 09:03:24 UTC

修复崩溃是一个单独的主题，但对于这个问题，我想知道：

1. 监视这种情况发生的最佳方法是什么？我们可以扫描日志中的关键字，但最好直接检查正在重置的统计信息，因为这会捕获此错误以及其他错误。仅仅是在至少一张表上手动运行分析然后定期轮询analyze_count = 0还是有更好的方法？
2. analyze如果我们确实检测到统计数据被重置（由于崩溃），那么手动重新填充统计数据似乎是个好主意，对吧？但是，为什么 PG 在从崩溃中恢复后不会自动执行此操作呢？
3. 或者无论如何定期运行 ANALYZE 是个好主意，比如说，每周甚至每晚？（目前在这个数据库上需要大约 200 秒——比备份时间少得多！）

我在 PostgreSQL 14 中有一个简单的分区表，如下所示：

create table entity
(
    dataset_id integer not null references dataset (id),
    ...
) partition by list (dataset_id);

create table entity_1
(
    like entity including constraints including defaults,
    constraint entity_1_pkey primary key (entity_id),
);

alter table entity attach partition entity_1 for values in (1);

创建一个分区的原因是先“分离”一个分区，然后在单独的语句中附加它是为了避免在父表上获得排他锁entity（分割。这是Laurenz Albe在https://stackoverflow.com/a/67016755/1536933中建议的解决方案，它非常适合创建分区。

不幸的是，我在删除分区时遇到了同样的问题：只要客户端正在从任何分区读取数据，我就无法删除任何其他分区：不仅被drop table entity_1阻塞，甚至alter table entity detach partition entity_1 concurrently被阻塞！我看不到pg_locks“同时分离”语句的任何相关锁定（没有granted=false），但 pgAdmin 仪表板显示Wait event: Lock: virtualxid和Blocking PIDs: (pid of the reading process)

从其他分区读取数据时如何删除分区？当然这应该是可能的？

我有一个只有整数和小整数列的表，最多加起来 20 个字节。pg_column_size报告 44. 由于每行开销 24 字节，这是预期的，如pg_column_size(table.*) 和 pg_column_size(table.col1) + pg_column_size (table.col2) 之间的差异中所述

但是，如果我除以pg_table_size行数，我会得到每行约 53 个字节，即使有数亿行。这些额外的 9 个字节从何而来？

即使只有一个整数列，我也可以重现这一点：

drop table if exists anint;

create table anint
as
select generate_series
from generate_series(1, 10000000);

select pg_column_size(e.*) -- 28
from anint e
limit 1;

select n_live_tup as row_count_estimate, -- 10000000
    pg_size_pretty(pg_table_size(relid)) as table_size, -- 346 MB
    case when n_live_tup = 0 then null else pg_table_size(relid) / cast(n_live_tup as float) end as table_bytes_per_row -- 36.2561536
from pg_catalog.pg_statio_user_tables io
join pg_catalog.pg_stat_user_tables s using (relid)
where io.schemaname = 'public' and io.relname = 'anint'

这将返回 ~36 字节/行，而不是 28。（在 PostgreSQL 14.1 上测试。）