Skip to content

Instantly share code, notes, and snippets.

@jnoortheen

jnoortheen/explain II - addition of hashjoin off.txt

Last active April 26, 2023 09:05
Show Gist options
  • Save jnoortheen/bb7a9df197f29ecf2b8204d33f77eec4 to your computer and use it in GitHub Desktop.
Save jnoortheen/bb7a9df197f29ecf2b8204d33f77eec4 to your computer and use it in GitHub Desktop.
Download ZIP
I - distributed query with -
Raw
explain II - addition of hashjoin off.txt
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=176101.644..176102.085 rows=25 loops=1)
Task Count: 1
Tuple data received from nodes: 34 kB
Tasks Shown: All
-> Task
Tuple data received from node: 34 kB
Node: host=localhost port=5432 dbname=parkpow_distributed
-> Limit (cost=1.31..110.89 rows=25 width=1864) (actual time=176034.509..176054.118 rows=25 loops=1)
-> Nested Loop Left Join (cost=1.31..5053134.93 rows=1152766 width=1864) (actual time=176034.500..176054.091 rows=25 loops=1)
-> Nested Loop (cost=1.16..5024978.57 rows=1152766 width=1829) (actual time=176033.200..176052.528 rows=25 loops=1)
-> Nested Loop Left Join (cost=0.73..2478829.56 rows=1152766 width=1661) (actual time=176026.641..176029.193 rows=25 loops=1)
-> Nested Loop Left Join (cost=0.58..2450070.56 rows=1152766 width=1511) (actual time=176026.568..176028.940 rows=25 loops=1)
-> Index Scan using main_visit_org_id_start_dt_end_dt_tmp_102172 on main_visit_102172 main_visit (cost=0.43..2421621.81 rows=1152766 width=1361) (actual time=176024.513..176026.444 rows=25 loops=1)
Index Cond: (org_id = 628)
Filter: (((start_date >= '2022-11-06 03:00:00+00'::timestamp with time zone) OR (end_date >= '2022-11-06 03:00:00+00'::timestamp with time zone)) AND ((start_date <= '2022-12-04 18:08:02.283238+00'::timestamp with time zone) OR (end_date <= '2022-12-04 18:08:02.283238+00'::timestamp with time zone)))
Rows Removed by Filter: 2268043
-> Memoize (cost=0.15..0.17 rows=1 width=150) (actual time=0.070..0.070 rows=1 loops=25)
Cache Key: main_visit.org_id, main_visit.start_cam_id
Cache Mode: logical
Hits: 16 Misses: 9 Evictions: 0 Overflows: 0 Memory Usage: 3kB
-> Index Scan using main_camera_pkey_102076 on main_camera_102076 main_camera (cost=0.14..0.16 rows=1 width=150) (actual time=0.125..0.125 rows=1 loops=9)
Index Cond: ((org_id = main_visit.org_id) AND (org_id = 628) AND (id = main_visit.start_cam_id))
-> Memoize (cost=0.15..0.17 rows=1 width=150) (actual time=0.006..0.006 rows=0 loops=25)
Cache Key: main_visit.org_id, main_visit.end_cam_id
Cache Mode: logical
Hits: 21 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using main_camera_pkey_102076 on main_camera_102076 t4 (cost=0.14..0.16 rows=1 width=150) (actual time=0.011..0.011 rows=1 loops=4)
Index Cond: ((org_id = main_visit.org_id) AND (org_id = 628) AND (id = main_visit.end_cam_id))
-> Index Scan using main_vehicle_idx_102140 on main_vehicle_102140 main_vehicle (cost=0.43..2.21 rows=1 width=168) (actual time=0.764..0.764 rows=1 loops=25)
Index Cond: (id = main_visit.vehicle_id)
Filter: (org_id = 628)
-> Memoize (cost=0.15..0.63 rows=1 width=35) (actual time=0.055..0.055 rows=1 loops=25)
Cache Key: main_camera.org_id, main_camera.parking_id
Cache Mode: logical
Hits: 21 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using main_parkinglot_pkey_102044 on main_parkinglot_102044 main_parkinglot (cost=0.14..0.62 rows=1 width=35) (actual time=0.315..0.316 rows=1 loops=4)
Index Cond: ((org_id = main_camera.org_id) AND (org_id = 628) AND (id = main_camera.parking_id))
Planning Time: 28.122 ms
Execution Time: 176056.568 ms
Planning Time: 22.445 ms
Execution Time: 176102.186 ms
(41 rows)
Raw
explain-I - citus.propagate_set_commands=local,enable_seqscan= off,local jit = off.txt
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=0 width=0) (actual time=196833.710..196833.720 rows=25 loops=1)
Task Count: 1
Tuple data received from nodes: 34 kB
Tasks Shown: All
-> Task
Tuple data received from node: 34 kB
Node: host=localhost port=5432 dbname=parkpow_distributed
-> Limit (cost=1.31..110.89 rows=25 width=1864) (actual time=196713.572..196750.450 rows=25 loops=1)
-> Nested Loop Left Join (cost=1.31..5053134.93 rows=1152766 width=1864) (actual time=196713.569..196750.415 rows=25 loops=1)
-> Nested Loop (cost=1.16..5024978.57 rows=1152766 width=1829) (actual time=196712.141..196748.588 rows=25 loops=1)
-> Nested Loop Left Join (cost=0.73..2478829.56 rows=1152766 width=1661) (actual time=196707.657..196710.459 rows=25 loops=1)
-> Nested Loop Left Join (cost=0.58..2450070.56 rows=1152766 width=1511) (actual time=196707.576..196710.172 rows=25 loops=1)
-> Index Scan using main_visit_org_id_start_dt_end_dt_tmp_102172 on main_visit_102172 main_visit (cost=0.43..2421621.81 rows=1152766 width=1361) (actual time=196703.881..196705.963 rows=25 loops=1)
Index Cond: (org_id = 628)
Filter: (((start_date >= '2022-11-06 03:00:00+00'::timestamp with time zone) OR (end_date >= '2022-11-06 03:00:00+00'::timestamp with time zone)) AND ((start_date <= '2022-12-04 18:08:02.283238+00'::timestamp with time zone) OR (end_date <= '2022-12-04 18:08:02.283238+00'::timestamp with time zone)))
Rows Removed by Filter: 2268043
-> Memoize (cost=0.15..0.17 rows=1 width=150) (actual time=0.159..0.159 rows=1 loops=25)
Cache Key: main_visit.org_id, main_visit.start_cam_id
Cache Mode: logical
Hits: 16 Misses: 9 Evictions: 0 Overflows: 0 Memory Usage: 3kB
-> Index Scan using main_camera_pkey_102076 on main_camera_102076 main_camera (cost=0.14..0.16 rows=1 width=150) (actual time=0.426..0.426 rows=1 loops=9)
Index Cond: ((org_id = main_visit.org_id) AND (org_id = 628) AND (id = main_visit.start_cam_id))
-> Memoize (cost=0.15..0.17 rows=1 width=150) (actual time=0.006..0.006 rows=0 loops=25)
Cache Key: main_visit.org_id, main_visit.end_cam_id
Cache Mode: logical
Hits: 21 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using main_camera_pkey_102076 on main_camera_102076 t4 (cost=0.14..0.16 rows=1 width=150) (actual time=0.019..0.019 rows=1 loops=4)
Index Cond: ((org_id = main_visit.org_id) AND (org_id = 628) AND (id = main_visit.end_cam_id))
-> Index Scan using main_vehicle_idx_102140 on main_vehicle_102140 main_vehicle (cost=0.43..2.21 rows=1 width=168) (actual time=1.518..1.518 rows=1 loops=25)
Index Cond: (id = main_visit.vehicle_id)
Filter: (org_id = 628)
-> Memoize (cost=0.15..0.63 rows=1 width=35) (actual time=0.064..0.064 rows=1 loops=25)
Cache Key: main_camera.org_id, main_camera.parking_id
Cache Mode: logical
Hits: 21 Misses: 4 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using main_parkinglot_pkey_102044 on main_parkinglot_102044 main_parkinglot (cost=0.14..0.62 rows=1 width=35) (actual time=0.365..0.365 rows=1 loops=4)
Index Cond: ((org_id = main_camera.org_id) AND (org_id = 628) AND (id = main_camera.parking_id))
Planning Time: 20.581 ms
Execution Time: 196752.370 ms
Planning Time: 31.436 ms
Execution Time: 196834.099 ms
(41 rows)
Raw
main_vehicle.txt
Table "public.main_vehicle"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+--------------------------+-----------+----------+------------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('main_vehicle_id_seq'::regclass) | plain | | |
license_plate | citext | | | | extended | | |
make | character varying(255) | | | | extended | | |
color | character varying(255) | | | | extended | | |
created | timestamp with time zone | | not null | | plain | | |
field1 | character varying(255) | | | | extended | | |
field2 | character varying(255) | | | | extended | | |
field3 | character varying(255) | | | | extended | | |
field4 | character varying(255) | | | | extended | | |
field5 | character varying(255) | | | | extended | | |
field6 | character varying(255) | | | | extended | | |
payment_status | smallint | | not null | | plain | | |
lp_fuzzy | character varying(255) | | | | extended | | |
type | character varying(20) | | not null | | extended | | |
model | character varying(255) | | | | extended | | |
data | jsonb | | not null | | extended | | |
region | character varying(255) | | | | extended | | |
org_id | bigint | | not null | | plain | | |
Indexes:
"main_vehicle_pkey" PRIMARY KEY, btree (org_id, id)
"main_vehicle_idx" btree (id)
"main_vehicle_license_plate_1363749a" btree (license_plate)
"main_vehicle_license_plate_ops" gin (org_id, license_plate gin_trgm_ops)
"main_vehicle_org_color_btree" btree (org_id, color)
"main_vehicle_org_id_6c12aaa6" btree (org_id)
"main_vehicle_org_id_license_plate_573efb6a_uniq" UNIQUE CONSTRAINT, btree (org_id, license_plate)
"main_vehicle_org_type_btree" btree (org_id, type)
"mv_org_make_trgm" gin (org_id, make gin_trgm_ops)
"mv_org_model_trgm" gin (org_id, model gin_trgm_ops)
"mv_org_region_trgm" gin (org_id, region gin_trgm_ops)
Check constraints:
"main_vehicle_payment_status_check" CHECK (payment_status >= 0)
Foreign-key constraints:
"main_vehicle_org_id_6c12aaa6_fk_users_organization_id" FOREIGN KEY (org_id) REFERENCES users_organization(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "main_alert" CONSTRAINT "main_alert_vehicle_id_org_id_76b29277_fk_main_vehicle_id_org_id" FOREIGN KEY (vehicle_id, org_id) REFERENCES main_vehicle(id, org_id) DEFERRABLE INITIALLY DEFERRED
TABLE "main_comment" CONSTRAINT "main_comment_vehicle_id_org_id_a2c46c58_fk_main_vehi" FOREIGN KEY (vehicle_id, org_id) REFERENCES main_vehicle(id, org_id) DEFERRABLE INITIALLY DEFERRED
TABLE "main_vehicletotags" CONSTRAINT "main_vehicletotags_vehicle_id_org_id_236601ac_fk_main_vehi" FOREIGN KEY (vehicle_id, org_id) REFERENCES main_vehicle(id, org_id) DEFERRABLE INITIALLY DEFERRED
TABLE "main_visit" CONSTRAINT "main_visit_vehicle_id_org_id_5be03733_fk_main_vehicle_id_org_id" FOREIGN KEY (vehicle_id, org_id) REFERENCES main_vehicle(id, org_id) DEFERRABLE INITIALLY DEFERRED
Access method: heap
Options: autovacuum_vacuum_scale_factor=0, autovacuum_vacuum_threshold=5000, autovacuum_analyze_scale_factor=0, autovacuum_analyze_threshold=100000
Raw
main_visit.txt
Table "public.main_visit"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
-------------------+--------------------------+-----------+----------+----------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('main_visit_id_seq'::regclass) | plain | | |
duration | double precision | | | | plain | | |
start_date | timestamp with time zone | | | | plain | | |
start_img | character varying(100) | | | | extended | | |
end_date | timestamp with time zone | | | | plain | | |
end_img | character varying(100) | | | | extended | | |
end_cam_id | integer | | | | plain | | |
start_cam_id | integer | | | | plain | | |
vehicle_id | integer | | not null | | plain | | |
end_data | jsonb | | | | extended | | |
start_data | jsonb | | | | extended | | |
score | double precision | | not null | | plain | | |
end_orientation | smallint | | | | plain | | |
start_orientation | smallint | | | | plain | | |
end_direction | smallint | | | | plain | | |
start_direction | smallint | | | | plain | | |
end_img_plate | character varying(100) | | | | extended | | |
start_img_plate | character varying(100) | | | | extended | | |
last_spotted | timestamp with time zone | | | | plain | | |
spot_id | integer | | | | plain | | |
org_id | bigint | | not null | | plain | | |
Indexes:
"main_visit_pkey" PRIMARY KEY, btree (org_id, id)
"main_visit_cfk_idx_org_end_cam_id" btree (org_id, end_cam_id)
"main_visit_cfk_idx_org_spot_id" btree (org_id, spot_id)
"main_visit_cfk_idx_org_start_cam_id" btree (org_id, start_cam_id)
"main_visit_cfk_idx_org_vehicle_id" btree (org_id, vehicle_id)
"main_visit_end_cam_id_18a7e3a5" btree (end_cam_id)
"main_visit_org_id_df5707aa" btree (org_id)
"main_visit_org_id_end_dt_tmp" btree (org_id, start_date)
"main_visit_org_id_start_dt_end_dt_tmp" btree (org_id, start_date DESC NULLS LAST, end_date DESC NULLS LAST)
"main_visit_org_id_start_dt_tmp" btree (org_id, start_date)
"main_visit_spot_id_2ceb7ec4" btree (spot_id)
"main_visit_start_cam_id_9f9d6d54" btree (start_cam_id)
"main_visit_vehicle_id_4c63db53" btree (vehicle_id)
Check constraints:
"main_visit_end_direction_check" CHECK (end_direction >= 0)
"main_visit_end_orientation_check" CHECK (end_orientation >= 0)
"main_visit_start_direction_check" CHECK (start_direction >= 0)
"main_visit_start_orientation_check" CHECK (start_orientation >= 0)
Foreign-key constraints:
"main_visit_end_cam_id_org_id_d1aa392e_fk_main_camera_id_org_id" FOREIGN KEY (end_cam_id, org_id) REFERENCES main_camera(id, org_id) DEFERRABLE INITIALLY DEFERRED
"main_visit_org_id_df5707aa_fk_users_organization_id" FOREIGN KEY (org_id) REFERENCES users_organization(id) DEFERRABLE INITIALLY DEFERRED
"main_visit_spot_id_org_id_b15bd42b_fk_main_spot_id_org_id" FOREIGN KEY (spot_id, org_id) REFERENCES main_spot(id, org_id) DEFERRABLE INITIALLY DEFERRED
"main_visit_start_cam_id_org_id_f0945510_fk_main_came" FOREIGN KEY (start_cam_id, org_id) REFERENCES main_camera(id, org_id) DEFERRABLE INITIALLY DEFERRED
"main_visit_vehicle_id_org_id_5be03733_fk_main_vehicle_id_org_id" FOREIGN KEY (vehicle_id, org_id) REFERENCES main_vehicle(id, org_id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
TABLE "main_alert" CONSTRAINT "main_alert_visit_id_org_id_c46574f5_fk_main_visit_id_org_id" FOREIGN KEY (visit_id, org_id) REFERENCES main_visit(id, org_id) DEFERRABLE INITIALLY DEFERRED
Access method: heap
Options: autovacuum_vacuum_scale_factor=0, autovacuum_vacuum_threshold=5000, autovacuum_analyze_scale_factor=0, autovacuum_analyze_threshold=100000
Raw
query.sql
explain analyze SELECT "main_visit"."id", "main_visit"."org_id", "main_visit"."vehicle_id", "main_visit"."score", "main_visit"."duration", "main_visit"."start_date", "main_visit"."start_cam_id", "main_visit"."start_img", "main_visit"."start_img_plate", "main_visit"."start_data", "main_visit"."start_orientation", "main_visit"."start_direction", "main_visit"."spot_id", "main_visit"."end_date", "main_visit"."end_cam_id", "main_visit"."end_img", "main_visit"."end_img_plate", "main_visit"."end_data", "main_visit"."end_orientation", "main_visit"."end_direction", "main_visit"."last_spotted", "main_vehicle"."id", "main_vehicle"."org_id", "main_vehicle"."region", "main_vehicle"."license_plate", "main_vehicle"."make", "main_vehicle"."model", "main_vehicle"."color", "main_vehicle"."type", "main_vehicle"."created", "main_vehicle"."payment_status", "main_vehicle"."field1", "main_vehicle"."field2", "main_vehicle"."field3", "main_vehicle"."field4", "main_vehicle"."field5", "main_vehicle"."field6", "main_vehicle"."lp_fuzzy", "main_vehicle"."data", "main_camera"."id", "main_camera"."org_id", "main_camera"."code", "main_camera"."name", "main_camera"."parking_id", "main_camera"."type", "main_camera"."vehicle_orientation", "main_camera"."latitude", "main_camera"."longitude", "main_camera"."notes", "main_camera"."min_dscore", "main_camera"."min_score", "main_camera"."min_spot_iou_index", "main_camera"."fuzziness_score", "main_camera"."mismatch_score", "main_camera"."image", "main_camera"."image_data", "main_camera"."absence_time", "main_camera"."spot_image", "main_camera"."anomaly_detection", "main_camera"."anomaly_sensitivity", "main_parkinglot"."id", "main_parkinglot"."org_id", "main_parkinglot"."name", "main_parkinglot"."parking_spaces", "main_parkinglot"."occ_reset_interval", "main_parkinglot"."occ_reset_interval_hr", "main_parkinglot"."order", "main_parkinglot"."absent_timer", T4."id", T4."org_id", T4."code", T4."name", T4."parking_id", T4."type", T4."vehicle_orientation", T4."latitude", T4."longitude", T4."notes", T4."min_dscore", T4."min_score", T4."min_spot_iou_index", T4."fuzziness_score", T4."mismatch_score", T4."image", T4."image_data", T4."absence_time", T4."spot_image", T4."anomaly_detection", T4."anomaly_sensitivity" FROM "main_visit" LEFT OUTER JOIN "main_camera" ON ("main_visit"."start_cam_id" = "main_camera"."id" AND ("main_visit"."org_id" = "main_camera"."org_id")) LEFT OUTER JOIN "main_camera" T4 ON ("main_visit"."end_cam_id" = T4."id" AND ("main_visit"."org_id" = T4."org_id")) INNER JOIN "main_vehicle" ON ("main_visit"."vehicle_id" = "main_vehicle"."id" AND ("main_visit"."org_id" = "main_vehicle"."org_id")) LEFT OUTER JOIN "main_parkinglot" ON ("main_camera"."parking_id" = "main_parkinglot"."id" AND ("main_camera"."org_id" = "main_parkinglot"."org_id")) WHERE ("main_visit"."org_id" = 628 AND ("main_visit"."start_date" >= '2022-11-06T00:00:00-03:00'::timestamptz OR "main_visit"."end_date" >= '2022-11-06T00:00:00-03:00'::timestamptz) AND ("main_visit"."start_date" <= '2022-12-04T14:08:02.283238-04:00'::timestamptz OR "main_visit"."end_date" <= '2022-12-04T14:08:02.283238-04:00'::timestamptz)) ORDER BY "main_visit"."start_date" DESC NULLS LAST, "main_visit"."end_date" DESC NULLS LAST LIMIT 25;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment