Postgresql
巨大的數據庫（> 600M 記錄/表）查詢超時或達到記憶體限制？
Stackexchange 最親愛的 DBA 朋友，
我正在嘗試將來自多個源表的數據寫入一個新表，其中包含我希望用作以後項目的源的複合資訊。然而，我面臨的問題是，在從多個來源創建數據時，我似乎在數據庫方面遇到了某種限制。
我使用的查詢如下：
INSERT INTO "Iml_Alex" ( tapin_oid, "StopAreaNumber_id", tapout_time, next_tapin, info_text_ref ) SELECT t1.tapin_oid, t1."StopAreaNumber_id", MIN ( t5."ObservedArrivalCalendarTimeKey" ), t1.next_tapin, t1.info_text_ref FROM db_name."Iml_trips_with_tapouts2" AS t1 LEFT JOIN db_name."ImlStopArea" AS t2 ON t1."StopAreaNumber_id" = t2."id" LEFT JOIN db_name."FactValidatedTickets_2017" AS t3 ON t3.oid = t1.tapin_oid LEFT JOIN db_name."FactValidatedTickets_2017" AS t4 ON t4.oid = t1.next_tapin LEFT JOIN ( SELECT "DimStopPoint"."StopAreaNumber",-- For StopArea Filtering "FactObservation"."VehicleKey",-- For FactValidatedTickets_2017 Filtering "FactObservation"."CalendarDateKey",-- For "FactValidatedTickets_2017" Filtering "FactObservation"."ObservedArrivalCalendarTimeKey" -- For "FactValidatedTickets_2017" Filterin FROM db_name."FactObservation" LEFT JOIN db_name."DimPoint" ON "FactObservation"."ObservedStopPointKey" = "DimPoint"."PointKey" LEFT JOIN db_name."DimStopPoint" ON "FactObservation"."ObservedStopPointKey" = "DimStopPoint"."StopPointKey" ) AS t5 ON t5."StopAreaNumber" = t2."StopAreaNumber" AND t5."VehicleKey" = t3."VehicleKey" AND t5."CalendarDateKey" = t3."CalendarDateKey" AND t5."ObservedArrivalCalendarTimeKey" > t3."ValidationCalendarTimeKey" GROUP BY tapin_oid現在，此查詢的目標是從組合表中找到到達該站點的合適匹配到達（首先到達受條件限制）並將其儲存在抽頭時間欄位中。因為我認為插入比更新原始表（iml_trips_with_tapouts2）中的值更快，所以我為此創建了一個新表 iml_alex。最初這很好用，在 0.02 秒內測試了 1000 條記錄，在 200 秒內測試了 100 萬條記錄，但是在接下來的 100 萬條記錄中，我在 7200 秒後遇到了一些超時，當我超過 120 萬條記錄時，我根本沒有完成任何事情。我的估計是需要一些優化，因為查詢中的表都比較大……
給出表大小的一些概念：Iml_trips_with_tapouts2 有 6.18 億條記錄，FactValidatedTickets2017 有 7 億條記錄，FactObservation 有大約 2 億條記錄。
可以假設查詢中的大多數欄位都被索引為 b-tree
有誰知道我如何重新制定查詢或使此查詢處理所有 6.18 億次旅行，找到各自的時間（如果適用）。請注意，6.18 億次旅行是半稀疏的。它們都有一個主鍵（oid），但並非所有它們實際上都有一個 StopAreaNumber_id，因為並非所有站點都已被檢測到。可以假設車站和停車車輛之間的聯繫也是如此，因為只有 25% 的車輛記錄了跟踪設備在一年中移動的時間
如文中所述，使用 LIMIT - OFFSET 沒有提供結果，可能是因為分頁在這些大小的表格上是一個糟糕的主意。但我不太確定如何讓所有記錄都執行。
PS這是有限集的計算EXPLAIN ANALYZE：
Insert on "Iml_Alex" (cost=93666763.69..562005567.10 rows=500000 width=28) (actual time=1591967.759..1591967.759 rows=0 loops=1) -> Subquery Scan on "*SELECT*" (cost=93666763.69..562005567.10 rows=500000 width=28) (actual time=249417.533..1589517.552 rows=500000 loops=1) -> Limit (cost=93666763.69..562000567.10 rows=500000 width=28) (actual time=249417.530..1588880.330 rows=500000 loops=1) -> GroupAggregate (cost=3.01..579476593582.24 rows=618657664 width=28) (actual time=3.488..1588237.083 rows=600000 loops=1) -> Nested Loop Left Join (cost=3.01..579467313717.28 rows=618657664 width=28) (actual time=3.271..1586737.513 rows=1322861 loops=1) -> Nested Loop Left Join (cost=2.44..574145856182.56 rows=618657664 width=28) (actual time=3.235..1567144.567 rows=1322861 loops=1) Join Filter: ("DimStopPoint"."StopAreaNumber" = t2."StopAreaNumber") Rows Removed by Join Filter: 213599279 -> Nested Loop Left Join (cost=1.44..12911071331.05 rows=618657664 width=40) (actual time=0.131..46894.358 rows=600001 loops=1) -> Nested Loop Left Join (cost=0.86..7589613796.33 rows=618657664 width=28) (actual time=0.064..7084.816 rows=600001 loops=1) -> Index Scan using "PK2_tapin_oid" on "Iml_trips_with_tapouts2" t1 (cost=0.57..2495918660.17 rows=618657664 width=24) (actual time=0.057..3930.349 rows=600001 loops=1) -> Index Scan using "PK_StopAreaNumber_id" on "ImlStopArea" t2 (cost=0.29..8.22 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=600001) Index Cond: (t1."StopAreaNumber_id" = id) -> Index Scan using factvalidatedtickets_2017_oid_index on "FactValidatedTickets_2017" t3 (cost=0.58..8.59 rows=1 width=16) (actual time=0.063..0.064 rows=1 loops=600001) Index Cond: (oid = (t1.tapin_oid)::oid) -> Nested Loop (cost=1.00..906.26 rows=74 width=16) (actual time=0.010..2.310 rows=357 loops=600001) -> Index Scan using factobsrvation_datevehicletime_index on "FactObservation" (cost=0.57..280.98 rows=74 width=16) (actual time=0.007..0.339 rows=357 loops=600001) Index Cond: (("CalendarDateKey" = t3."CalendarDateKey") AND ("VehicleKey" = t3."VehicleKey") AND ("ObservedArrivalCalendarTimeKey" > t3."ValidationCalendarTimeKey")) -> Index Scan using "PK_DimStopPoint" on "DimStopPoint" (cost=0.42..8.44 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=214466300) Index Cond: ("StopPointKey" = "FactObservation"."ObservedStopPointKey") -> Index Only Scan using factvalidatedtickets_2017_oid_index on "FactValidatedTickets_2017" t4 (cost=0.58..8.59 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=1322861) Index Cond: (oid = (t1.next_tapin)::oid) Heap Fetches: 1317025 Trigger for constraint FKA_date: time=653.066 calls=500000 Trigger for constraint FKA_time: time=1334.542 calls=500000 Trigger for constraint FKA_to_stoparea_id: time=2547.736 calls=500000 Total runtime: 1597365.296 ms更多資訊：（一些）表定義：
-- ---------------------------- -- Table structure for FactValidatedTickets_2017 -- ---------------------------- DROP TABLE IF EXISTS "db_name"."FactValidatedTickets_2017"; CREATE TABLE "db_name"."FactValidatedTickets_2017" ( "CalendarDateKey" int4 NOT NULL, "ValidationProductKey" int4 NOT NULL, "ValidationDeviceLocationKey" int4 NOT NULL, "ValidationEquipmentKey" int4 NOT NULL, "VehicleKey" int4 NOT NULL, "PointKey" int4 NOT NULL, "LineKey" int4 NOT NULL, "ServiceJourneyPatternVariantKey" int4 NOT NULL, "ValidationCalendarTimeKey" int4 NOT NULL, "DepartureCalendarTimeKey" int4 NOT NULL, "SourceSystemKey" int4 NOT NULL, "DateDay" int4 NOT NULL, "ValidationTransactionID" varchar(50) COLLATE "pg_catalog"."default" NOT NULL, "CardKey" int4 NOT NULL, "NumberOfValidations" int4, "InsertAuditKey" int4 NOT NULL, "UpdateAuditKey" int4, "NumberOfValidations2" int4 DEFAULT 0, "DepartureKey" int4, "DepartureMatched" bit(1), "TicketSerialNumber" int8 ) WITH (OIDS=TRUE) ; ALTER TABLE "db_name"."FactValidatedTickets_2017" CLUSTER ON "factvalidatedtickets_2017_cardid_date_index"; -- ---------------------------- -- Indexes structure for table FactValidatedTickets_2017 -- ---------------------------- CREATE INDEX "factvalidatedtickets_2017_cardid_date_index" ON "db_name"."FactValidatedTickets_2017" USING btree ( "CardKey" "pg_catalog"."int4_ops" ASC NULLS LAST, "CalendarDateKey" "pg_catalog"."int4_ops" ASC NULLS LAST ) WITH (FILLFACTOR = 100); ALTER TABLE "db_name"."FactValidatedTickets_2017" CLUSTER ON "factvalidatedtickets_2017_cardid_date_index"; CREATE INDEX "factvalidatedtickets_2017_datevehicletime_index" ON "db_name"."FactValidatedTickets_2017" USING btree ( "CalendarDateKey" "pg_catalog"."int4_ops" ASC NULLS LAST, "VehicleKey" "pg_catalog"."int4_ops" ASC NULLS LAST, "DepartureCalendarTimeKey" "pg_catalog"."int4_ops" ASC NULLS LAST ) WITH (FILLFACTOR = 100) TABLESPACE "disk2"; CREATE INDEX "factvalidatedtickets_2017_oid_index" ON "db_name"."FactValidatedTickets_2017" USING btree ( oid "pg_catalog"."oid_ops" ASC NULLS LAST ) WITH (FILLFACTOR = 100); CREATE INDEX "factvalidatedtickets_2017_vehiclekey_index" ON "db_name"."FactValidatedTickets_2017" USING btree ( "VehicleKey" "pg_catalog"."int4_ops" ASC NULLS LAST ) WITH (FILLFACTOR = 100) TABLESPACE "disk2"; -- ---------------------------- -- Primary Key structure for table FactValidatedTickets_2017 -- ---------------------------- ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "PK_FactValidatedTickets_2017" PRIMARY KEY ("CalendarDateKey", "CardKey", "SourceSystemKey", "ValidationTransactionID"); -- ---------------------------- -- Foreign Keys structure for table FactValidatedTickets_2017 -- ---------------------------- ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_2017_DepartureCalendarTimeKey" FOREIGN KEY ("DepartureCalendarTimeKey") REFERENCES "db_name"."DimCalendarTime" ("CalendarTimeKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_2017_InsertAuditKey" FOREIGN KEY ("InsertAuditKey") REFERENCES "db_name"."DimAudit" ("AuditKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_2017_LineKey" FOREIGN KEY ("LineKey") REFERENCES "db_name"."DimLine" ("LineKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_2017_PointKey" FOREIGN KEY ("PointKey") REFERENCES "db_name"."DimPoint" ("PointKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_2017_ServiceJourneyPatternVariantKey" FOREIGN KEY ("ServiceJourneyPatternVariantKey") REFERENCES "db_name"."DimServiceJourneyPatternVariant" ("ServiceJourneyPatternVariantKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_2017_SourceSystemKey" FOREIGN KEY ("SourceSystemKey") REFERENCES "db_name"."DimSourceSystem" ("SourceSystemKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_2017_UpdateAuditKey" FOREIGN KEY ("UpdateAuditKey") REFERENCES "db_name"."DimAudit" ("AuditKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_2017_ValidationCalendarTimeKey" FOREIGN KEY ("ValidationCalendarTimeKey") REFERENCES "db_name"."DimCalendarTime" ("CalendarTimeKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_2017_ValidationDeviceLocationKey" FOREIGN KEY ("ValidationDeviceLocationKey") REFERENCES "db_name"."DimValidationDeviceLocation" ("ValidationDeviceLocationKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_2017_ValidationEquipmentKey" FOREIGN KEY ("ValidationEquipmentKey") REFERENCES "db_name"."DimValidationEquipment" ("ValidationEquipmentKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_2017_ValidationProductKey" FOREIGN KEY ("ValidationProductKey") REFERENCES "db_name"."DimValidationProduct" ("ValidationProductKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_2017_VehicleKey" FOREIGN KEY ("VehicleKey") REFERENCES "db_name"."DimVehicle" ("VehicleKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_CalendarDateKey_2017" FOREIGN KEY ("CalendarDateKey") REFERENCES "db_name"."DimCalendarDate" ("CalendarDateKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactValidatedTickets_2017" ADD CONSTRAINT "FK_FactValidatedTickets_CardKey_2017" FOREIGN KEY ("CardKey") REFERENCES "db_name"."DimCard" ("CardKey") ON DELETE NO ACTION ON UPDATE NO ACTION;事實觀察：
-- ---------------------------- -- Table structure for FactObservation -- ---------------------------- DROP TABLE IF EXISTS "db_name"."FactObservation"; CREATE TABLE "db_name"."FactObservation" ( "CalendarDateKey" int4 NOT NULL, "DepartureKey" int4 NOT NULL, "PreviousStopPointKey" int4 NOT NULL, "ObservedStopPointKey" int4 NOT NULL, "ObservationTypeKey" int4 NOT NULL, "VehicleKey" int4 NOT NULL, "ScheduledDepartureCalendarTimeKey" int4 NOT NULL, "ObservedDepartureCalendarTimeKey" int4 NOT NULL, "ScheduledArrivalCalendarTimeKey" int4 NOT NULL, "ObservedArrivalCalendarTimeKey" int4 NOT NULL, "ScheduledDepartureTrafficTimeKey" int4 NOT NULL, "ObservedDepartureTrafficTimeKey" int4 NOT NULL, "ScheduledArrivalTrafficTimeKey" int4 NOT NULL, "ObservedArrivalTrafficTimeKey" int4 NOT NULL, "CarOrderPos" varchar(1) COLLATE "pg_catalog"."default" NOT NULL, "DateDay" int4 NOT NULL, "ObservationID" varchar(50) COLLATE "pg_catalog"."default", "ObservationPostID" varchar(50) COLLATE "pg_catalog"."default", "DwellTime" int4, "StopTime" int4, "Boardings" int4, "Alightings" int4, "CurrentLoad" int4, "Speed" int4, "CoveredDistance" int4, "RunTime" int4, "InsertAuditKey" int4 NOT NULL, "UpdateAuditKey" int4, "MetroJourneyKey" int4 NOT NULL, "ATRflag" int4, "MTSflag" int4, "CalendarDateKey2" int4 NOT NULL, "ReadFileID" int4, "LogPackageKey" int4, "LineKey" int4, "Completeness" int2, "LogicalStatusKey" int4, "Boarding_org" int4, "Alighting_org" int4, "Load_org" int4, "Gps_latitude" numeric(10,6), "Gps_longitude" numeric(10,6) ) WITH (OIDS=TRUE) ; -- ---------------------------- -- Indexes structure for table FactObservation -- ---------------------------- CREATE INDEX "factobservation_date_index" ON "db_name"."FactObservation" USING btree ( "CalendarDateKey" "pg_catalog"."int4_ops" ASC NULLS LAST ) WITH (FILLFACTOR = 100); CREATE INDEX "factobservation_observedarrivalcalendartimekey_index" ON "db_name"."FactObservation" USING btree ( "ObservedArrivalCalendarTimeKey" "pg_catalog"."int4_ops" ASC NULLS LAST ) WITH (FILLFACTOR = 100); CREATE INDEX "factobservation_observedstoppoint_index" ON "db_name"."FactObservation" USING btree ( "ObservedStopPointKey" "pg_catalog"."int4_ops" ASC NULLS LAST ) WITH (FILLFACTOR = 100); CREATE INDEX "factobservation_vehicle_index" ON "db_name"."FactObservation" USING btree ( "VehicleKey" "pg_catalog"."int4_ops" ASC NULLS LAST ) WITH (FILLFACTOR = 100); CREATE INDEX "factobsrvation_datevehicletime_index" ON "db_name"."FactObservation" USING btree ( "CalendarDateKey" "pg_catalog"."int4_ops" ASC NULLS LAST, "VehicleKey" "pg_catalog"."int4_ops" ASC NULLS LAST, "ObservedArrivalCalendarTimeKey" "pg_catalog"."int4_ops" ASC NULLS LAST ) WITH (FILLFACTOR = 100) TABLESPACE "disk2"; CREATE INDEX "factobsrvation_stopdatevehicletime_index" ON "db_name"."FactObservation" USING btree ( "ObservedStopPointKey" "pg_catalog"."int4_ops" ASC NULLS LAST, "VehicleKey" "pg_catalog"."int4_ops" ASC NULLS LAST, "CalendarDateKey" "pg_catalog"."int4_ops" ASC NULLS LAST, "ObservedArrivalCalendarTimeKey" "pg_catalog"."int4_ops" ASC NULLS LAST ) WITH (FILLFACTOR = 100) TABLESPACE "disk2"; -- ---------------------------- -- Primary Key structure for table FactObservation -- ---------------------------- ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "PK_FactObservation" PRIMARY KEY ("CalendarDateKey2", "CarOrderPos", "DepartureKey", "ObservedStopPointKey"); -- ---------------------------- -- Foreign Keys structure for table FactObservation -- ---------------------------- ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_CalendarDateKey" FOREIGN KEY ("CalendarDateKey") REFERENCES "db_name"."DimCalendarDate" ("CalendarDateKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_CalendarDateKey2" FOREIGN KEY ("CalendarDateKey2") REFERENCES "db_name"."DimCalendarDate" ("CalendarDateKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_DepartureKey" FOREIGN KEY ("DepartureKey") REFERENCES "db_name"."DimDeparture" ("DepartureKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_InsertAuditKey" FOREIGN KEY ("InsertAuditKey") REFERENCES "db_name"."DimAudit" ("AuditKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_LineKey" FOREIGN KEY ("LineKey") REFERENCES "db_name"."DimLine" ("LineKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_LogicalStatusKey" FOREIGN KEY ("LogicalStatusKey") REFERENCES "db_name"."DimObservationLogicalStatus" ("LogicalStatusKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_MetroJourneyKey" FOREIGN KEY ("MetroJourneyKey") REFERENCES "db_name"."DimMetroJourney" ("MetroJourneyKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_ObservationTypeKey" FOREIGN KEY ("ObservationTypeKey") REFERENCES "db_name"."DimObservationType" ("ObservationTypeKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_ObservedArrivalCalendarTimeKey" FOREIGN KEY ("ObservedArrivalCalendarTimeKey") REFERENCES "db_name"."DimCalendarTime" ("CalendarTimeKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_ObservedArrivalTrafficTimeKey" FOREIGN KEY ("ObservedArrivalTrafficTimeKey") REFERENCES "db_name"."DimTrafficTime" ("TrafficTimeKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_ObservedDepartureCalendarTimeKey" FOREIGN KEY ("ObservedDepartureCalendarTimeKey") REFERENCES "db_name"."DimCalendarTime" ("CalendarTimeKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_ObservedDepartureTrafficTimeKey" FOREIGN KEY ("ObservedDepartureTrafficTimeKey") REFERENCES "db_name"."DimTrafficTime" ("TrafficTimeKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_ObservedStopPointKey" FOREIGN KEY ("ObservedStopPointKey") REFERENCES "db_name"."DimStopPoint" ("StopPointKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_PreviousStopPointKey" FOREIGN KEY ("PreviousStopPointKey") REFERENCES "db_name"."DimStopPoint" ("StopPointKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_ScheduledArrivalCalendarTimeKey" FOREIGN KEY ("ScheduledArrivalCalendarTimeKey") REFERENCES "db_name"."DimCalendarTime" ("CalendarTimeKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_ScheduledArrivalTrafficTimeKey" FOREIGN KEY ("ScheduledArrivalTrafficTimeKey") REFERENCES "db_name"."DimTrafficTime" ("TrafficTimeKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_ScheduledDepartureCalendarTimeKey" FOREIGN KEY ("ScheduledDepartureCalendarTimeKey") REFERENCES "db_name"."DimCalendarTime" ("CalendarTimeKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_ScheduledDepartureTrafficTimeKey" FOREIGN KEY ("ScheduledDepartureTrafficTimeKey") REFERENCES "db_name"."DimTrafficTime" ("TrafficTimeKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_UpdateAuditKey" FOREIGN KEY ("UpdateAuditKey") REFERENCES "db_name"."DimAudit" ("AuditKey") ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE "db_name"."FactObservation" ADD CONSTRAINT "FK_FactObservation_VehicleKey" FOREIGN KEY ("VehicleKey") REFERENCES "db_name"."DimVehicle" ("VehicleKey") ON DELETE NO ACTION ON UPDATE NO ACTION;
您的查詢中有相當多的**噪音和不必要的複雜性。**除其他外，加入
"FactValidatedTickets_2017"並"DimPoint"似乎是徒勞的。這應該是等效的並且更快（未經測試）：INSERT INTO "Iml_Alex" (tapin_oid, "StopAreaNumber_id", tapout_time , next_tapin, info_text_ref) SELECT t1.tapin_oid, t1."StopAreaNumber_id", MIN(t5."ObservedArrivalCalendarTimeKey" ), t1.next_tapin, t1.info_text_ref FROM sl_data."Iml_trips_with_tapouts2" t1 LEFT JOIN sl_data."FactValidatedTickets_2017" t2 ON t2.oid = t1.tapin_oid LEFT JOIN sl_data."ImlStopArea" t3 ON t3."id" = t1."StopAreaNumber_id" LEFT JOIN sl_data."DimStopPoint" t4 ON t4."StopAreaNumber" = t3."StopAreaNumber" LEFT JOIN sl_data."FactObservation" t5 ON t5."ObservedStopPointKey" = t4."StopPointKey" AND t5."VehicleKey" = t2."VehicleKey" AND t5."CalendarDateKey" = t2."CalendarDateKey" AND t5."ObservedArrivalCalendarTimeKey" > t2."ValidationCalendarTimeKey" GROUP BY t1.tapin_oid;
tapin_oid必須是 PK 或者您必須在 GROUP BY 列表中列出所有非聚合列。看：根據實際的表定義和數據分佈，
LEFT JOIN LATERAL可能會有所幫助（自 Postgres 9.3 起可用）。看：您**
OID**在查詢中使用“FactValidatedTickets_2017”。我根本不會使用 OID。將所有表更改WITH (OIDS=FALSE)為自 Postgres 8.0 (!) 以來的預設值，改用 PK。引用手冊：在使用者表中使用 OID 被認為已棄用
準確的索引定義對您的查詢至關重要，但未公開。您似乎需要大多數索引。多列索引應該是有用的：
CREATE INDEX ON sl_data."FactObservation" ("ObservedStopPointKey", "VehicleKey", "CalendarDateKey", "ObservedArrivalCalendarTimeKey");
"ObservedArrivalCalendarTimeKey"必須是最後一個索引列。看：在您添加的查詢計劃中，使用了名為的類似索引
factobsrvation_datevehicletime_index，但沒有"ObservedStopPointKey".無論您做什麼，都緊急考慮升級到目前版本。Postgres 9.3 很舊，在 2018 年達到 EOL。從那以後，大數據有了許多實質性的改進。
可能還有更多，比如伺服器配置，但我覺得我已經做得夠多了。