主页 / user-23110

ldrg's questions

Martin Hope
ldrg
Asked: 2021-09-30 11:30:16 +0800 CST
  • 0

该pg_stats表显示 PostgreSQL 在收集统计信息时存储了一个直方图。这意味着 PostgreSQL 可以通过计算包含过滤器值的直方图桶的数量来估计过滤器将返回多少行。您可以使用 增加直方图桶的数量，ALTER TABLE ... SET STATISTICS这意味着 PostgreSQL 可以更精确地了解过滤器的选择性（估计更少的行）。

查询计划器估计执行查询所需完成的工作总量，并将其与阈值进行比较，以查看是否应该进行 JIT 查询编译。这个估计值是否包括 PostgreSQL 为筛选选择性目的而维护的行数估计值？当 JIT 编译的性能优势超过 JIT 编译的启动时间时，增加统计样本大小会让查询规划器做出更好的决策吗？

postgresql
Martin Hope
ldrg
Asked: 2021-03-08 14:41:46 +0800 CST
  • 1

有一篇流行且看似权威的博客文章On Rocks and Sand关于如何优化 PostgreSQL 表的大小以通过重新排序列长度来消除内部填充。他们解释了如果可变长度类型不在表格末尾，它们如何产生一些额外的填充：

这意味着我们可以整天链接可变长度的列，而无需在右侧边界处引入填充。因此，我们可以推断出可变长度列不会引入膨胀，只要它们位于列列表的末尾。

在文章的最后，总结一下：

按照 pg_type 中定义的类型长度对列进行排序。

有一个与 Ruby 的 ActiveRecord 集成的库，可以自动重新排序列以减少填充，称为pg_column_byte_packer。您可以在该 repo 中看到 README 引用了上述博客文章，并且通常与博客文章描述的内容相同。

但是，pg_column_byte_packer返回的结果与它引用的博客文章不一致。这篇博文取自 PostgreSQL 的内部pg_type.typelen，它通过 -1 的对齐方式将可变长度列始终放在末尾。pg_column_byte_packer给他们一个对齐3。

pg_column_byte_packer有解释性评论：

    # These types generally have an alignment of 4 (as designated by pg_type
    # having a typalign value of 'i', but they're special in that small values
    # have an optimized storage layout. Beyond the optimized storage layout, though,
    # these small values also are not required to respect the alignment the type
    # would otherwise have. Specifically, values with a size of at most 127 bytes
    # aren't aligned. That 127 byte cap, however, includes an overhead byte to store
    # the length, and so in reality the max is 126 bytes. Interestingly TOASTable
    # values are also treated that way, but we don't have a good way of knowing which
    # values those will be.
    #
    # See: `fill_val()` in src/backend/access/common/heaptuple.c (in the conditional
    # `else if (att->attlen == -1)` branch.
    #
    # When no limit modifier has been applied we don't have a good heuristic for
    # determining which columns are likely to be long or short, so we currently
    # just slot them all after the columns we believe will always be long.

评论似乎没有错，因为文本列的 apg_type.typalign值为 4，但它们也有pg_type.typlen-1 的值，博客文章认为在表格末尾时获得最佳包装。

因此，对于具有一integer列、一text列和一smallint列的表，pg_column_byte_packer会将文本列放在两者之间。他们甚至有一个单元测试来断言这种情况总是会发生。

我的问题是：什么列的顺序实际上是为最小的空间打包的？来自的评论pg_column_byte_packer似乎没有错，因为文本列的 apg_type.typalign值为 4，但它们的 apg_type.typlen值为 -1。

postgresql
Martin Hope
ldrg
Asked: 2019-09-12 12:20:29 +0800 CST
  • 0

MySQL 的命令行工具有一个标志--i-am-a-dummy设置执行限制：

使用 --safe-updates，您只能通过指定标识它们的键值或 LIMIT 子句或两者来修改行。这有助于防止事故。安全更新模式还限制产生（或估计产生）非常大的结果集的 SELECT 语句。

有没有办法在 psql 中打开类似的功能？

postgresql psql
Martin Hope
ldrg
Asked: 2019-01-16 07:02:23 +0800 CST
  • 1

数据库小提琴链接。我有很多这样的表：

CREATE TABLE house_to_cats (
  id SERIAL PRIMARY KEY,
  house_id INTEGER,
  cat_id INTEGER
);

-- house with cats 1 and 2: too small
INSERT INTO house_to_cats (house_id, cat_id) VALUES (1, 1), (1, 2);
-- house with cats 1 2 3 4: too big
INSERT INTO house_to_cats (house_id, cat_id) VALUES (2, 1), (2, 2), (2, 3), (2, 4);
-- house with cats 1 2 3: just right
INSERT INTO house_to_cats (house_id, cat_id) VALUES (3, 1), (3, 2), (3, 3);

我需要一个查询，该查询采用任意猫列表并返回匹配的房子（如果存在）。我想出了这个：

SELECT
    house_id
FROM (
    SELECT
          house_id
        , ARRAY_AGG(cat_id) as cat_id_agg
    FROM house_to_cats
    JOIN (
        SELECT DISTINCT
              house_id
        FROM house_to_cats
        JOIN (SELECT * FROM UNNEST(ARRAY[1, 2, 3]) cat_id) inn USING (cat_id)
    ) filter USING (house_id)
    GROUP BY house_id
) agg
WHERE cat_id_agg <@ ARRAY[1, 2, 3]
  AND cat_id_agg @> ARRAY[1, 2, 3];

有一个更好的方法吗？

我的查询背后的想法：在 中filter，获取house_id其中至少有一只我们的猫。在中，为所有这些agg创建数组。并在最外层的查询中过滤掉与我们的集合不匹配的组。cat_id_agghouse_ids

postgresql optimization
Martin Hope
ldrg
Asked: 2017-11-06 08:42:47 +0800 CST
  • 4

这是对以下内容的后续：PostgreSQL 中的覆盖索引是否有助于 JOIN 列？

考虑在连接表中过滤的另一个问题中模式的逆：

CREATE TABLE thing_types(
   id              INTEGER PRIMARY KEY
 , first_lvl_type  TEXT
 , second_lvl_type TEXT
);

CREATE TABLE things(
   id         INTEGER PRIMARY KEY
 , thing_type INTEGER REFERENCES thing_types(id)
 , t1c1       INTEGER
);

像这样的查询：

SELECT things.t1c1
FROM   things
JOIN   thing_types ON things.thing_type = thing_types.id
WHERE  thing_types.first_lvl_type = 'Book'
AND    thing_types.second_lvl_type = 'Biography';

有一个像这样的索引是不是很疯狂：

CREATE INDEX ON thing_types(first_lvl_type, second_lvl_type, id);

哪个涵盖了用于该连接的主键？该索引会被用作覆盖索引来帮助JOIN上述查询吗？当我知道要JOIN像这样编辑表时，是否应该更改索引策略以更频繁地覆盖主键？

postgresql index
Martin Hope
ldrg
Asked: 2017-11-05 19:18:50 +0800 CST
  • 16

我有很多看起来像这样的表格：

CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER);
CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES table1(id), t2c1 INTEGER);

我做了很多连接，我试图过滤加入的表以从第一个表中获取内容，如下所示：

SELECT t1c1
FROM table1
JOIN table2 ON table2.t1 = table1.id
WHERE t2c1 = 42;

当我为表编写索引时，我会查看在 WHERE 子句中使用的列并构建索引以满足它们。所以对于这个查询，我最终会写一个这样的索引：

CREATE INDEX ON table2 (t2c1);

并且该索引至少可以在该查询中使用。

我的问题是，如果我编写这样的索引：

CREATE INDEX ON table2 (t2c1, t1);

是否会将该索引用作覆盖索引来帮助上述查询中的 JOIN？我应该改变我的索引编写策略来覆盖外键列吗？

postgresql index
Martin Hope
ldrg
Asked: 2017-10-12 20:55:23 +0800 CST
  • 0

我有一张桌子

CREATE TABLE (game, team, player, position);

如果我创建一个唯一索引，(game, player, position)我会得到一个表，在同一场比赛中，同一名球员不能为两支球队打同一个位置。这很有用，但仍然有可能有人插入一行，其中同一名球员打两个不同的位置，每支球队一个。

是否可以编写某种唯一索引或约束，使同一名球员不可能在一场比赛中为两支球队效力？

postgresql index
Martin Hope
ldrg
Asked: 2013-04-28 12:48:18 +0800 CST
  • 14

我在文档count(*)中看到和之间的区别count(pk)。我一直在使用count(pk)(where pkis a SERIAL PRIMARY KEY) 不知道count(*).

我的问题是关于 Postgres 的内部优化。是否足够聪明地发现 aSERIAL PRIMARY KEY将存在于每一行中并且永远不会是错误的并且只计算行数，或者它是否会对每一行进行冗余谓词检查？我同意这可能是一个毫无意义的优化，但我只是好奇。

我查看了EXPLAIN和EXPLAIN VERBOSEfor的输出count(*)，看看是否提到检查其输出中的谓词。它没有。count(id)count(id > 50)EXPLAIN

postgresql optimization