postgresql - 为什么 PostgreSQL 不为此连接查询使用索引

Question

explain (analyze) select
    event_user_detail.*

from event_user_detail
inner join guest_list on event_user_detail.guest_list_id = guest_list.id

where
    guest_list.event_id=2985739029

以下查询计划的结果:

Gather  (cost=1052.56..43408.58 rows=244 width=97) (actual time=66.570..67.810 rows=0 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Nested Loop  (cost=52.56..42384.18 rows=102 width=97) (actual time=57.183..57.183 rows=0 loops=3)
        ->  Parallel Seq Scan on guest_list  (cost=0.00..13151.33 rows=5 width=8) (actual time=56.941..57.169 rows=2 loops=3)
              Filter: (event_id = '2985739029'::bigint)
              Rows Removed by Filter: 254489
        ->  Bitmap Heap Scan on event_user_detail  (cost=52.56..5830.93 rows=1564 width=97) (actual time=0.007..0.007 rows=0 loops=5)
              Recheck Cond: (guest_list_id = guest_list.id)
              ->  Bitmap Index Scan on idx_event_user_detail_guest_list_id  (cost=0.00..52.17 rows=1564 width=0) (actual time=0.005..0.005 rows=0 loops=5)
                    Index Cond: (guest_list_id = guest_list.id)
Planning time: 0.252 ms
Execution time: 67.838 ms

即使在 guest_list(event_id) 上有一个索引。有人可以解释为什么会发生这种情况，是否有解决方法？

如果我将其分成 2 个查询，其中一个只是获取 guest_list id，然后执行一个简单的 in (...ids)，那么查询会非常快。我尝试对子查询执行相同的操作，但我认为优化器将其变成了连接。

-- ----------------------------
-- Table structure for guest_list
-- ----------------------------
DROP TABLE IF EXISTS "public"."guest_list";
CREATE TABLE "public"."guest_list" (
  "id" int8 NOT NULL,
  "creation_date" timestamp(6),
  "last_modification_date" timestamp(6),
  "uuid" uuid,
  "deleted" bool NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "version" int4,
  "event_id" int8,
  "permanent_guest_list_id" int8,
  "color" varchar(255) COLLATE "pg_catalog"."default"
)
;

-- ----------------------------
-- Indexes structure for table guest_list
-- ----------------------------
CREATE INDEX "idx_guest_list_event_id" ON "public"."guest_list" USING btree (
  "event_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);
CREATE INDEX "idx_guest_list_permanent_guest_list_id" ON "public"."guest_list" USING btree (
  "permanent_guest_list_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);

-- ----------------------------
-- Uniques structure for table guest_list
-- ----------------------------
ALTER TABLE "public"."guest_list" ADD CONSTRAINT "uk_o4sa0dw6lcdjv96gl2p96xwki" UNIQUE ("uuid");

-- ----------------------------
-- Primary Key structure for table guest_list
-- ----------------------------
ALTER TABLE "public"."guest_list" ADD CONSTRAINT "guest_list_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Foreign Keys structure for table guest_list
-- ----------------------------
ALTER TABLE "public"."guest_list" ADD CONSTRAINT "fk7tk6fxgyo4h7ykelb9c0pe5ap" FOREIGN KEY ("event_id") REFERENCES "public"."event" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "public"."guest_list" ADD CONSTRAINT "guest_list_permanent_guest_list_id_fkey" FOREIGN KEY ("permanent_guest_list_id") REFERENCES "public"."permanent_guest_list" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

-- ----------------------------
-- Table structure for event_user_detail
-- ----------------------------
DROP TABLE IF EXISTS "public"."event_user_detail";
CREATE TABLE "public"."event_user_detail" (
  "id" int8 NOT NULL,
  "creation_date" timestamp(6),
  "last_modification_date" timestamp(6),
  "uuid" uuid,
  "deleted" bool NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "value" text COLLATE "pg_catalog"."default",
  "version" int4,
  "event_id" int8,
  "user_id" int8,
  "guest_list_id" int8,
  "reference_user_id" int8
)
;

-- ----------------------------
-- Indexes structure for table event_user_detail
-- ----------------------------
CREATE INDEX "idx_event_user_detail_deleted" ON "public"."event_user_detail" USING btree (
  "deleted" "pg_catalog"."bool_ops" ASC NULLS LAST
);
CREATE INDEX "idx_event_user_detail_event_id" ON "public"."event_user_detail" USING btree (
  "event_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);
CREATE INDEX "idx_event_user_detail_guest_list_id" ON "public"."event_user_detail" USING btree (
  "guest_list_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);
CREATE INDEX "idx_event_user_detail_user_id" ON "public"."event_user_detail" USING btree (
  "user_id" "pg_catalog"."int8_ops" ASC NULLS LAST
);

-- ----------------------------
-- Uniques structure for table event_user_detail
-- ----------------------------
ALTER TABLE "public"."event_user_detail" ADD CONSTRAINT "uk_orfh8fkwtk681af38a65everr" UNIQUE ("uuid");

-- ----------------------------
-- Primary Key structure for table event_user_detail
-- ----------------------------
ALTER TABLE "public"."event_user_detail" ADD CONSTRAINT "event_user_detail_pkey" PRIMARY KEY ("id");

-- ----------------------------
-- Foreign Keys structure for table event_user_detail
-- ----------------------------
ALTER TABLE "public"."event_user_detail" ADD CONSTRAINT "fk8bffonom9l1fgcanegl9nm641" FOREIGN KEY ("user_id") REFERENCES "public"."user" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "public"."event_user_detail" ADD CONSTRAINT "fk_event_user_detail_guest_list_id" FOREIGN KEY ("guest_list_id") REFERENCES "public"."guest_list" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "public"."event_user_detail" ADD CONSTRAINT "fk_event_user_detail_reference_user_id" FOREIGN KEY ("reference_user_id") REFERENCES "public"."user" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "public"."event_user_detail" ADD CONSTRAINT "fkisr2ccpapw537ntw4c0mlytcw" FOREIGN KEY ("event_id") REFERENCES "public"."event" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

Answer 1

它大大高估了要为 guest_list 中的每一行在 event_user_detail 中查找的行数(可能是因为 guest_list 中有 一些 行在 event_user_detail 中确实有很多条目，只是不是您在此处选择的那些)。它认为它将要找到的大量行使并行查询看起来很有吸引力，但获得该并行查询的方法是在 guest_list 上使用 seq 扫描。这就是它的作用。

您可以通过将 max_parallel_workers_per_gather 设置为 0 来禁用并行查询。如果您无论如何都没有从并行查询中获得太多好处，这对您来说可能是一个很好的“真实”解决方案。如果您确实从中受益并且不想禁用它，那么您至少可以在当前 session 中执行此操作以查看我的理论是否正确。