I have a series of update statements that follow a general pattern: one update aggregates values from another table (or sometimes multiple tables), the next update produces a rank based on the aggregated values. This process is repeated 23 times for a total 46 update statements. Each update couplet takes 30-40 seconds to run on its own, but when I run them all together as a single transaction via PgAdmin it takes over an hour instead of the ~15 mins I would expect based on the individual query times. (I ended up stopping execution and running them separately last time I tried.)
If I run the same set of updates in a file via psql the process completes in the expected 15 min timeframe.
Is there some quirk to the query planner that would change the execution plan based on there being a lot of update statements running in a single transaction? Given the different behavior between psql and PgAdmin, I assume this is related to the way the queries are packaged for execution but I'm not familiar enough to know the difference.
Is there a way to write my code so as to improve performance when running it all as a single transaction through PgAdmin?
I'm on PostgreSQL 9.5 on Ubuntu 16.04.
Here are two example couplets from the code:
-- bike_driver_aggressive
UPDATE generated.crash_aggregates
SET bike_driver_aggressive = (
SELECT COUNT(*)
FROM crashes_bike2 c
WHERE c.int_id = crash_aggregates.int_id
AND c.aggressive_driverfault
);
WITH ranks AS (
SELECT int_id,
rank() OVER (ORDER BY bike_driver_aggressive DESC) AS rank
FROM crash_aggregates
)
UPDATE generated.crash_aggregates
SET bike_driver_aggressive_rank = ranks.rank
FROM ranks
WHERE crash_aggregates.int_id = ranks.int_id;
-- bike_allinjury
UPDATE generated.crash_aggregates
SET bike_allinjury = (
SELECT COUNT(*)
FROM crashes_bike1 c
WHERE c.int_id = crash_aggregates.int_id
AND c.injurycrash
) + (
SELECT COUNT(*)
FROM crashes_bike2 c
WHERE c.int_id = crash_aggregates.int_id
AND c.injurycrash
);
WITH ranks AS (
SELECT int_id,
rank() OVER (ORDER BY bike_allinjury DESC) AS rank
FROM crash_aggregates
)
UPDATE generated.crash_aggregates
SET bike_allinjury_rank = ranks.rank
FROM ranks
WHERE crash_aggregates.int_id = ranks.int_id;
The crash_aggregates table is created thusly:
CREATE TABLE crash_aggregates
(
int_id integer NOT NULL,
geom geometry(Point,2231),
bike_driver_aggressive integer,
bike_driver_aggressive_rank integer,
bike_driver_failyield integer,
bike_driver_failyield_rank integer,
bike_driver_disregardsignal integer,
bike_driver_disregardsignal_rank integer,
bike_highspeed integer,
bike_highspeed_rank integer,
bike_biker_aggressive integer,
bike_biker_aggressive_rank integer,
bike_biker_failyield integer,
bike_biker_failyield_rank integer,
bike_biker_disregardsignal integer,
bike_biker_disregardsignal_rank integer,
bike_influence integer,
bike_influence_rank integer,
bike_driver_distracted integer,
bike_driver_distracted_rank integer,
bike_driver_reckless integer,
bike_driver_reckless_rank integer,
bike_tbone integer,
bike_tbone_rank integer,
bike_opp_lhook integer,
bike_opp_lhook_rank integer,
bike_samedir integer,
bike_samedir_rank integer,
bike_samedir_rhook1 integer,
bike_samedir_rhook1_rank integer,
bike_samedir_rhook2 integer,
bike_samedir_rhook2_rank integer,
bike_perp_rhook integer,
bike_perp_rhook_rank integer,
bike_perp_rhook_swalk1 integer,
bike_perp_rhook_swalk1_rank integer,
bike_perp_rhook_swalk2 integer,
bike_perp_rhook_swalk2_rank integer,
bike_tbone_swalk1 integer,
bike_tbone_swalk1_rank integer,
bike_tbone_swalk2 integer,
bike_tbone_swalk2_rank integer,
bike_allfatal integer,
bike_allfatal_rank integer,
bike_allinjury integer,
bike_allinjury_rank integer,
bike_injuryfatal integer,
bike_injuryfatal_rank integer,
bike_top10 integer,
bike_num1s integer,
bike_num2s integer,
bike_num3s integer,
bike_num4s integer,
bike_num5s integer,
bike_num6s integer,
bike_num7s integer,
bike_num8s integer,
bike_num9s integer,
bike_num10s integer,
CONSTRAINT crash_aggregates_pkey PRIMARY KEY (int_id)
);
The bike1 table:
CREATE TABLE received.crashes_bike1
(
caseid text,
year integer,
unittype_one text,
unittype_two text,
unittype_three text,
circumstance text,
primary_contrib text,
condition_1 text,
condition_2 text,
condition_3 text,
dirfromint text,
diroftravel_one text,
diroftravel_two text,
tdg_directions text,
dir_key integer,
directions text,
bike_mvmt text,
veh_mvmt text,
comb_mvmt text,
comb_mvmt_sw text,
map_code integer,
injury boolean,
diroftravel_three text,
disabled_st1 text,
disabled_st2 text,
contrib_1 text,
contrib_2 text,
contrib_3 text,
enteredby text,
entereddate text,
estvehspeed_one integer,
estvehspeed_two integer,
estvehspeed_three integer,
feetfromint integer,
firstharmful text,
mostharmful text,
secondharmful text,
internamedir text,
lightingcondition text,
location text,
masterid integer,
precrashmaneuv_1 text,
precrashmaneuv_2 text,
precrashmaneuv_3 text,
node integer,
numberinjured integer,
numberoffatalities integer,
pedaction_one text,
pedaction_two text,
pedaction_three text,
publicproperty text,
railroadcrossing text,
roadcondition text,
roadcontour text,
roaddescription text,
roadsurface text,
safetyequipmenthelmet_one text,
safetyequipmenthelmet_two text,
safetyequipmenthelmet_three text,
safetyequipsystem_one text,
safetyequipsystem_two text,
safetyequipsystem_three text,
safetyequipuse_one text,
safetyequipuse_two text,
safetyequipuse_three text,
speedlimit_one integer,
speedlimit_two integer,
speedlimit_three integer,
street1 integer,
street2 integer,
streetname_st1 text,
streetname_st2 text,
street_intersection text,
totvehs integer,
unitage_one integer,
unitage_two integer,
unitage_three integer,
vehcomb_one text,
vehcomb_two text,
vehcomb_three text,
vehicledefect_one text,
vehicledefect_two text,
vehicledefect_three text,
technicaljudgement text,
notes text,
typology text,
same_dir boolean,
opp_dir boolean,
perpen boolean,
angle boolean,
notes2 text,
sw text,
ww_sw text,
cw_dwy_alley text,
day_week text,
weekday text,
trail_access text,
bike_s_veh_s_st_p boolean,
bike_s_veh_lt_st_od boolean,
bike_s_veh_rt_st_p boolean,
bike_s_veh_rt_st_sd boolean,
bike_s_veh_s_st_sd boolean,
bike_s_veh_rt_st_ww_p boolean,
bike_s_veh_s_sw_ww_p boolean,
bike_s_veh_rt_sw_ww_p boolean,
highspeed boolean,
injurycrash boolean,
id integer NOT NULL DEFAULT nextval('crashes_bike1_id_seq'::regclass),
road_id1 integer,
road_id2 integer,
at_intersection boolean,
int_id integer,
CONSTRAINT crashes_bike1_pkey PRIMARY KEY (id)
);
CREATE INDEX idx_crashbike1bsvlso
ON received.crashes_bike1
USING btree
(bike_s_veh_lt_st_od);
CREATE INDEX idx_crashbike1bsvrsp
ON received.crashes_bike1
USING btree
(bike_s_veh_rt_st_p);
CREATE INDEX idx_crashbike1bsvrssd
ON received.crashes_bike1
USING btree
(bike_s_veh_rt_st_sd);
CREATE INDEX idx_crashbike1bsvrswp
ON received.crashes_bike1
USING btree
(bike_s_veh_rt_sw_ww_p);
CREATE INDEX idx_crashbike1bsvssp
ON received.crashes_bike1
USING btree
(bike_s_veh_s_st_p);
CREATE INDEX idx_crashbike1bsvsstsd
ON received.crashes_bike1
USING btree
(bike_s_veh_s_st_sd);
CREATE INDEX idx_crashbike1bsvsswwwp
ON received.crashes_bike1
USING btree
(bike_s_veh_s_sw_ww_p);
CREATE INDEX idx_crashbike1inj
ON received.crashes_bike1
USING btree
(injurycrash);
CREATE INDEX idx_crashbike1int
ON received.crashes_bike1
USING btree
(int_id);
The bike2 table:
CREATE TABLE received.crashes_bike2
(
accidentdate date,
accidenttime time without time zone,
adverseweather text,
appovertaketurn text,
caseid text,
constructionzone text,
contribfact_one text,
contribfact_two text,
contribfact_three text,
dirfromint text,
diroftravel_one text,
diroftravel_two text,
diroftravel_three text,
disabled_st1 text,
disabled_st2 text,
driveraction_one text,
driveraction_two text,
enteredby text,
entereddate text,
estvehspeed_one integer,
estvehspeed_two integer,
estvehspeed_three integer,
feetfromint integer,
firstharmful text,
mostharmful text,
secondharmful text,
internamedir text,
lightingcondition text,
location text,
masterid integer,
node integer,
numberinjured integer,
numberoffatalities integer,
roadcontour text,
roaddescription text,
roadsurface text,
rownum integer,
speedlimit_one integer,
speedlimit_two integer,
speedlimit_three integer,
street1 integer,
street2 integer,
streetname_st1 text,
streetname_st2 text,
totvehs integer,
unitage_one integer,
unitage_two integer,
unitage_three integer,
vehcomb_one text,
vehcomb_two text,
vehcomb_three text,
unittype_one text,
unittype_two text,
unittype_three text,
movement_one text,
movement_two text,
movement_three text,
circumstance text,
sw text,
othercw text,
motoristplacement text,
relationshipofplacements text,
wwswriding text,
bicyclelane text,
dooring text,
bicyclewwstreetriding text,
crashmonth integer,
crashday text,
hour time without time zone,
ridinglocation text,
crashyear integer,
injurycrash boolean,
fatalcrash boolean,
noinjuryfatality boolean,
unit1_veh boolean,
unit2_veh boolean,
unit1_bike boolean,
unit2_bike boolean,
intdistance_ft text,
hrgrp text,
bicyclist boolean,
bikeaction_one text,
bikeaction_two text,
newdriveraction_one text,
newdriveraction_two text,
bikeaction_one2 text,
bikeaction_two2 integer,
newbikeaction text,
newdriveraction_one2 text,
newdriveraction_two2 integer,
newdriveraction2 text,
bike_movement text,
driver_movement text,
unit1 text,
unit2 text,
unit3 text,
complex boolean,
nobike boolean,
bike_movement2 text,
bike_fault boolean,
driver_movement2 text,
crashtype text,
relationship text,
ww boolean,
xwalk boolean,
bikelane text,
sidewalk boolean,
location2 text,
direction text,
newcrashtype text,
bike_s_veh_s_st_p boolean,
bike_s_veh_lt_st_od boolean,
bike_s_veh_rt_st_p boolean,
bike_s_veh_rt_st_sd boolean,
bike_s_veh_s_st_sd boolean,
bike_s_veh_rt_st_ww_p boolean,
highspeed boolean,
atfault text,
influence boolean,
distracted_driverfault boolean,
aggressive_driverfault boolean,
inexperience_bikerfault boolean,
aggressive_bikerfault boolean,
failyield_driverfault boolean,
carereckless_driverfault boolean,
disregardsignal_driverfault boolean,
failyield_bikerfault boolean,
disregardsignal_bikerfault boolean,
allothercrashtype boolean,
bike_s_veh_rt_sw_ww_p boolean,
bike_s_veh_s_sw_ww_p boolean,
id integer NOT NULL DEFAULT nextval('crashes_bike2_id_seq'::regclass),
road_id1 integer,
road_id2 integer,
at_intersection boolean,
int_id integer,
CONSTRAINT crashes_bike2_pkey PRIMARY KEY (id)
);
CREATE INDEX idx_crashbike2aggbkflt
ON received.crashes_bike2
USING btree
(aggressive_bikerfault);
CREATE INDEX idx_crashbike2bsvhrtstsd
ON received.crashes_bike2
USING btree
(bike_s_veh_rt_st_sd);
CREATE INDEX idx_crashbike2bsvlso
ON received.crashes_bike2
USING btree
(bike_s_veh_lt_st_od);
CREATE INDEX idx_crashbike2bsvrsp
ON received.crashes_bike2
USING btree
(bike_s_veh_rt_st_p);
CREATE INDEX idx_crashbike2bsvrswp
ON received.crashes_bike2
USING btree
(bike_s_veh_rt_sw_ww_p);
CREATE INDEX idx_crashbike2bsvssp
ON received.crashes_bike2
USING btree
(bike_s_veh_s_st_p);
CREATE INDEX idx_crashbike2bsvsstsd
ON received.crashes_bike2
USING btree
(bike_s_veh_s_st_sd);
CREATE INDEX idx_crashbike2bsvsswwwp
ON received.crashes_bike2
USING btree
(bike_s_veh_s_sw_ww_p);
CREATE INDEX idx_crashbike2carreckdrv
ON received.crashes_bike2
USING btree
(carereckless_driverfault);
CREATE INDEX idx_crashbike2drvflt
ON received.crashes_bike2
USING btree
(aggressive_driverfault);
CREATE INDEX idx_crashbike2dsrgdsgndrv
ON received.crashes_bike2
USING btree
(disregardsignal_driverfault);
CREATE INDEX idx_crashbike2dsrgsgnbk
ON received.crashes_bike2
USING btree
(disregardsignal_bikerfault);
CREATE INDEX idx_crashbike2dstdrv
ON received.crashes_bike2
USING btree
(distracted_driverfault);
CREATE INDEX idx_crashbike2dui
ON received.crashes_bike2
USING btree
(influence);
CREATE INDEX idx_crashbike2flyldbik
ON received.crashes_bike2
USING btree
(failyield_bikerfault);
CREATE INDEX idx_crashbike2flylddrv
ON received.crashes_bike2
USING btree
(failyield_driverfault);
CREATE INDEX idx_crashbike2ftl
ON received.crashes_bike2
USING btree
(fatalcrash);
CREATE INDEX idx_crashbike2hispd
ON received.crashes_bike2
USING btree
(highspeed);
CREATE INDEX idx_crashbike2inj
ON received.crashes_bike2
USING btree
(injurycrash);
CREATE INDEX idx_crashbike2int
ON received.crashes_bike2
USING btree
(int_id);