0

Dearest DBA friends of Stackexchange,

I am trying to write data from multiple sourcetables into a new table containing compound information that i wish to use as source for a later project. The issue i face however is that i seem to hit some sort of limit on the database side when creating the data from multiple sources.

The query i used is the following:

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 

Now the goal of this query is to find a suitable matching arrival at that stop from a combined table (first arrival subject to conditions) and store this in the tapout-time field. Because i thought insert into is faster then updating values in the original table (iml_trips_with_tapouts2) i created a new table iml_alex for this purpose. Initially this worked great, tested on 1000 records in 0.02s, 1mln records in 200s, but when doing the next 1mln i hit some timeout after 7200s and when i get past 1.2mln i never get anything done at all. My estimate is that some optimisation is required as the tables in the query are all relatively huge...

To give some idea of the table sizes: Iml_trips_with_tapouts2 has 618mln records, FactValidatedTickets2017 has 700mln records FactObservation has about 200mln.

One can assume that most fields in the query are indexed as b-tree

Does anyone know how i can either reformulate the query or make this query process all 618mln trips finding their respective times (if applicable). Do note that the 618mln trips is semi-sparse. all of them have a primairy key (oid) but not all of them actually have a StopAreaNumber_id as not all stations have been detected. Same can be assumed for the link between stations and vehicles stopping, as only 25% of vehicles has registered its times as the tracking equipment shifted over the year

As mentioned in the text, using LIMIT - OFFSET did not provide results, likely due to pagination being a terrible idea on these sized tables. But i am not really sure how to make this run for all records.

P.S. Here is the computed EXPLAIN ANALYSE for limited set:

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

More info: (some) table Definitions:

-- ----------------------------
-- 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;

FactObservation:

-- ----------------------------
-- 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;
  • PostgreSQL 9.3.24 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit – Alex Vermeulen Mar 6 '19 at 8:55
  • That query can certainly profit from a much bigger work_mem, did you try increasing that? – a_horse_with_no_name Mar 6 '19 at 9:14
  • define much bigger work_mem and how much do you recon is required – Alex Vermeulen Mar 6 '19 at 9:38
  • The question regarding work_mem is mostly as i dont see any external merge Disk: <XXXX>kB happening like suggested here: andreigridnev.com/blog/… – Alex Vermeulen Mar 6 '19 at 10:38
  • You will only see external merge in the plan if you run explain (analyze), not with a "simple" explain – a_horse_with_no_name Mar 6 '19 at 10:54
3

There is quite a bit of noise and unnecessary complication in your query. Among other things joining to "FactValidatedTickets_2017" and "DimPoint" seems to be work for nothing. This should be equivalent and faster (untested):

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 must be the PK or you have to list all non-aggregated columns in the GROUP BY list. See:

Depending on actual table definitions and data distribution, LEFT JOIN LATERAL might help (available since Postgres 9.3). See:

You are using the OID of "FactValidatedTickets_2017" in the query. I would not operate with OIDs at all. Change all your tables to WITH (OIDS=FALSE) as has been the default since Postgres 8.0 (!), use a PK instead. Quoting the manual:

The use of OIDs in user tables is considered deprecated

Exact index definitions are crucial to your query but undisclosed. You seem to have most indexes needed. A multicolumn index should be instrumental:

CREATE INDEX ON sl_data."FactObservation" ("ObservedStopPointKey", "VehicleKey", "CalendarDateKey", "ObservedArrivalCalendarTimeKey");  

"ObservedArrivalCalendarTimeKey" must be the last index column. See:

In your added query plan similar index named factobsrvation_datevehicletime_index is used, but without "ObservedStopPointKey".

Whatever else you do, urgently consider upgrading to a current version. Postgres 9.3 is old, reached EOL in 2018. There have been many substantial improvements for big data since.

There is probably more, like server configuration, but I feel I have done enough.

| improve this answer | |
  • ERROR: missing FROM-clause entry for table "fo" LINE 4: MIN ( fo."ObservedArrivalCalendarTimeKey" ), Just to clarify: fo is the alias for FactObservation (t5?) > ERROR: column "t1.tapin_oid" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: t1.tapin_oid, And there the fun begins as i believe the min() function requires elements to be grouped and hmmz Second question relates to OID: How is using OID worse then adding a column (GUID or so) with a number? they both are a single identifier for a record and oid isnt slower then adding a column is it? – Alex Vermeulen Mar 7 '19 at 8:56
  • 2
    OIDs are deprecated, end of story. Use a serial, a bigserial or a uuid. – dland Mar 7 '19 at 11:08
  • fair enough dland. Still leaves the how to aggregate and still get all outputs? – Alex Vermeulen Mar 7 '19 at 12:35
  • @Alex: Yes, t5, and group by was missing. – Erwin Brandstetter Mar 7 '19 at 12:45
  • to clarify and be sure: group by t1.tapin_oid as that seems to work. Thanks for the input. the index is generated and ill let the query run for a test-set to compare results. Thanks for the input so far though. marked the answer as +1 for usefull and correct – Alex Vermeulen Mar 7 '19 at 13:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.