優化 Postgresql 查詢

我正在努力使以下查詢表現良好。children即使連接列上有索引，它也會對錶進行順序掃描。有任何想法嗎？

EXPLAIN ANALYZE SELECT "children".* from "children"
   INNER JOIN "parents" ON "parents"."id" = "children"."parent_id" 
   WHERE "parents"."partner_pk" = 'partner' 


Hash Join  (cost=55541.97..140831.44 rows=20804 width=168) (actual time=366.809..1851.311 rows=21215 loops=1)
 Hash Cond: (children.parent_id = parents.id)
 Buffers: shared hit=47476
 ->  Seq Scan on children  (cost=0.00..66250.30 rows=2152130 width=168) (actual time=0.007..1012.502 rows=2215906 loops=1)
       Buffers: shared hit=44729
 ->  Hash  (cost=55282.99..55282.99 rows=20718 width=4) (actual time=17.496..17.496 rows=21215 loops=1)
       Buckets: 4096  Batches: 1  Memory Usage: 746kB
       Buffers: shared hit=2747
       ->  Bitmap Heap Scan on parents  (cost=876.99..55282.99 rows=20718 width=4) (actual time=2.339..10.817 rows=21215 loops=1)
             Recheck Cond: ((partner_pk)::text = 'partner'::text)
             Heap Blocks: exact=2614
             Buffers: shared hit=2747
             ->  Bitmap Index Scan on index_parents_on_partner_pk  (cost=0.00..871.81 rows=20718 width=0) (actual time=1.992..1.992 rows=21215 loops=1)
                   Index Cond: ((partner_pk)::text = 'partner'::text)
                   Buffers: shared hit=133
Planning time: 0.280 ms
Execution time: 1855.458 ms



CREATE TABLE "public"."parents" (
   "id" int4 NOT NULL DEFAULT nextval('parents_id_seq'::regclass),
   "email" varchar NOT NULL,
   "partner_id" int4,
   "partner_pk" varchar
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."parents" OWNER TO "bark";
CREATE UNIQUE INDEX "index_parents_on_LOWER_email" ON "public"."parents" USING btree("lower(email::text)" ASC NULLS LAST);
CREATE INDEX "index_parents_on_partner_id" ON "public"."parents" USING btree(partner_id ASC NULLS LAST);
CREATE INDEX "index_parents_on_partner_pk" ON "public"."parents" USING btree(partner_pk ASC NULLS LAST);

CREATE TABLE "public"."children" (
   "id" int4 NOT NULL DEFAULT nextval('children_id_seq'::regclass),
   "first_name" varchar,
   "last_name" varchar,
   "parent_id" int4,
   "email" varchar,
)

CREATE INDEX "index_children_on_parent_id" ON "public"."children" USING btree(parent_id ASC NULLS LAST);

對子表進行 seq 掃描的明顯替代方法是嵌套循環，在該循環中，它必須按索引 20,718 次單獨探測子表。（實際上是 21,215 次，但當然規劃者在做出決定時並不知道這一點）。這些探測器中的每一個都將轉到索引中假定的隨機葉頁，然後跟踪子表本身中的一個隨機點，觸發一堆隨機 IO。如果所有這些數據都已在記憶體中找到，那麼這將是一個不錯的計劃。但是，如果它實際上必須為每個磁碟敲擊，這將是非常慢的（特別是因為您的磁碟似乎由沙鼠驅動 - 您的預編輯計劃需要 22 秒來對子表進行 seq 掃描，這很簡單可怕）。

如果要查看替代計劃，可以set enable_hashjoin = off在執行查詢之前。這通常不是您想要在生產中進行的設置，它更多地用於探索目的。但作為最後的手段，您可以在本地更改此設置以執行此查詢，然後將其重置。

如果你喜歡嵌套循環，因為你的大部分數據實際上大部分時間都在記憶體中，你可以通過將“random_page_cost”的值減小到接近 1 的值來向規劃器發出信號。在我手中，從預設值降低4 到 1.4 足以將計劃從 seq 掃描翻轉到嵌套循環。如果您的大部分數據始終在記憶體中（不僅僅是子表及其索引），那麼對這個參數進行全域更改可能是合理的。但是，如果你這樣做了，你應該有一個計劃來預熱你的數據庫，以防你重新啟動機器（比如這個）。以自然方式將數據恢復到記憶體中可能既緩慢又痛苦。

引用自：https://dba.stackexchange.com/questions/218085