慢速指数扫描

Question

我们有下表，其中包含25 We行并在生产中增长：

create table "Common"."Event"
(
    "Id" bigserial not null
        constraint "PK_Event"
            primary key,
    "AggregateId" citext not null,
    "AggregateType" integer not null,
    "CreatedBy" citext not null,
    "CreatedUtc" timestamp not null,
    "Data" jsonb,
    "EventId" uuid not null,
    "PropertyId" citext,
    "Type" citext not null,
    "UpdatedBy" citext not null,
    "UpdatedUtc" timestamp not null,
    "AccountCode" text not null,
    "TrackingId" citext not null,
    "OriginClientId" citext not null,
    "OriginSubjectId" citext
);

create index "IX_Event_AccountCode_AggregateType_Type_PropertyId_TrackingId"
    on "Common"."Event" ("AccountCode", "AggregateType", "Type", "PropertyId", "TrackingId");

create index "IX_Event_AccountCode_AggregateType_Type_PropertyId_AggregateId"
    on "Common"."Event" ("AccountCode", "AggregateType", "Type", "PropertyId", "AggregateId");

执行以下查询需要时间：

SELECT e."Id", e."AggregateId", e."CreatedUtc", e."OriginClientId", e."OriginSubjectId", e."PropertyId", e."Type"
FROM "Common"."Event" AS e
WHERE e."AccountCode" = 'XXXX'
  AND e."AggregateType" = 1
  AND e."Type" IN ('type-1', 'type-2', 'type-3', 'type-4')
  AND e."PropertyId" = 'YYYY'
ORDER BY e."Id" DESC
LIMIT 100 OFFSET 0;

并生成以下执行计划：

在某些情况下，查询比这个要慢得多。再次执行查询非常快，在更改偏移量时也是如此。

查看索引扫描输出：

我们注意到，为了让postgres只扫描索引，我们可以从一个覆盖索引中获益。然后，我们创建了以下覆盖索引：

create index "IX_Event_Test_Covering_Index"
    on "Common"."Event" ("AccountCode", "AggregateType", "Type", "PropertyId", "AggregateId", "OriginClientId", "OriginSubjectId", "CreatedUtc", "Id");

它产生以下执行计划：

挺酷的。

我们还运行VACUUM ANALYZE，并尝试为多个列创建统计信息，以使postgres更好地估计行数，但除了覆盖索引之外，没有任何帮助。

所以，现在的问题是，你们是否认为这将是解决这一问题的正确方法，或者我们是否遗漏了什么，而且有更好的解决办法。

我不确定我们拥有的数据量是否足以证明已经对表进行了分区。

Answer 1

索引扫描比我能推荐的任何东西都快，特别是在清空表之后。

但是，这个包含表中超过一半列的大型索引似乎有点过头了。

如果你想做得更便宜，可以试试B树索引，比如

CREATE INDEX ON "Common"."Event" ("AccountCode", "AggregateType", "PropertyId", "Type");

如果这些条件中的任何一个不是选择性的(也就是说，条件不移除许多行)，则从索引中省略它。

重要的是，"Type"是索引列列表中的最后一个。

Answer 2

我不认为仅索引扫描方法有什么问题，但您可能必须调整自动清空设置，以确保表在任何时候都是可见的。您是否需要在其中包含比其他列更频繁更新的列？如果是这样的话，包括这可能是一个问题。

另一种可能是利用这个限制，按顺序读取索引，然后在积累了100行满足所有条件的行后停止。但是IN-list会干扰按顺序读取索引，所以您(或者计划者，如果同时设置了两个索引)将不得不决定哪个更重要，直接从索引、“类型”列表限制还是按"Id“排序。那么，你的“类型”列表限制有多大的选择性？规划者认为这有多严格？

那就意味着

("AccountCode","AggregateType","PropertyId","Id")

此外，您还可以在任何其他列的末尾进行调整，比如只进行索引扫描，或者只是“类型”，这样就可以在不访问表的情况下消除索引中的行。

你的名单是从哪来的？如果它是为所有此类查询设置的，那么您可以在index WHERE子句中使用该列表构建一个部分索引(这意味着它将不再干扰有效的排序)。如果是用户提供的(比如网页上的复选框列表)，那是行不通的。

还有其他更高级的选项，但它们需要以难以理解和难以正确修改的扭曲方式编写查询，而且可能不值得。