2

I'm running this update script in PostgreSQL 9.6:

do $$
begin
    create temporary table if not exists tmp_rutting1 (
        road_id integer,
        direction_id integer,
        begin_km double precision,
        end_km double precision,
        owp_max_rut_depth double precision,
        iwp_max_rut_depth double precision);

    insert into tmp_rutting1
    (
    select  rut.road_id, 
        case when rut.direction_id in (1,2) then 1
        else 2
        end as direction_id,
        case when rut.direction_id in (1,2) then rut.begin_km
        else
            rut.end_km
        end as begin_km, 
        case when rut.direction_id in (1,2) then rut.end_km
        else rut.begin_km
        end as end_km, 
        rut.owp_max_rut_depth, rut.iwp_max_rut_depth
        from pms.pms_raw_rutting rut left join rrs_road r on r.id = rut.road_id
        where r.network_id = 9 and r.road_type_id = 1   
        );

    CREATE INDEX idx_tmp_rutting1 ON tmp_rutting1 USING btree (road_id, direction_id, begin_km, end_km);

    update md.md_video upd
    set lrut = rutting.owp_max_rut_depth, rrut = rutting.iwp_max_rut_depth
    from tmp_rutting1 as rutting
    where upd.road_id = rutting.road_id

    and upd.direction_id = rutting.direction_id                 -- Scenario 1: terminated after 4 700 000ms 
    --and upd.direction_id = 1 and rutting.direction_id = 1             -- Scenario 2: 250 783ms
    --and upd.direction_id = 2 and rutting.direction_id = 2             -- Scenario 3: 53 300ms
    and upd.chainage_km >= rutting.begin_km 
    and upd.chainage_km < rutting.end_km;

    drop table tmp_rutting1;
end $$

The temp table gets 1 435 065 records, and takes 3 710ms to create, fill and index.

The query takes more than an 2 hours, then I terminated it, because that doesn't make sense.

Notice the 2 commented lines. If I uncomment them, one at a time, the script takes about 5 minutes (exact times shown in brackets).

The direction_id field only contains either a 1 or a 2. No null direction_id values exist. Not in the tmp table, nor in the 'update' table.

Why does it take so much longer (actually it doesn't even complete) with the upd.direction_id = rutting.direction_id statement in the where clause?

The following links shows the explain diagrams for the 3 scenarios. One would expect Scenario 2 and 3 to have the same diagrams, but they differ dramatically.

Explain Diagrams and Query Plans

Here is the layout of the table being updated:

CREATE TABLE md.md_video
(
  id serial NOT NULL,
  road_id integer,
  latitude double precision,
  longitude double precision,
  altitude double precision,
  begin_km double precision,
  end_km double precision,
  chainage_km double precision,
  frame_number integer,
  liri double precision,
  riri double precision,
  iri double precision,
  lrut double precision,
  rrut double precision,
  path_id integer,
  survey_date date,
  lane_nr integer,
  direction_id integer,
  new_road_id integer,
  pd_video character varying,
  import_comment character varying,
  camera_direction integer,
  CONSTRAINT pk_md_video PRIMARY KEY (id),
  CONSTRAINT "Ref_md_video_to_vid_path" FOREIGN KEY (path_id)
      REFERENCES md.vid_path (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE md.md_video
  OWNER TO postgres;

-- Index: md.idx_md_video

-- DROP INDEX md.idx_md_video;

CREATE INDEX idx_md_video
  ON md.md_video
  USING btree
  (road_id, direction_id, chainage_km);

-- Index: md.ui_md_video

-- DROP INDEX md.ui_md_video;

CREATE UNIQUE INDEX ui_md_video
  ON md.md_video
  USING btree
  (road_id, camera_direction, direction_id, lane_nr, chainage_km, survey_date);

Your Answer

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

Browse other questions tagged or ask your own question.