explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 29U8 : Brian's Highway Truck Query

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 144.555 ↑ 1.0 1 1

Sort (cost=6,176.00..6,176.00 rows=1 width=264) (actual time=144.554..144.555 rows=1 loops=1)

  • Sort Key: "*SELECT* 1".name
  • Sort Method: quicksort Memory: 25kB
2. 0.068 144.545 ↑ 1.0 1 1

Nested Loop Semi Join (cost=48.64..6,175.99 rows=1 width=264) (actual time=92.035..144.545 rows=1 loops=1)

  • Join Filter: (("*SELECT* 1_1"._key)::text = "*SELECT* 1_2".asset__key)
  • Rows Removed by Join Filter: 130
3. 0.145 143.929 ↑ 1.0 1 1

Nested Loop (cost=48.21..5,527.24 rows=1 width=386) (actual time=91.420..143.929 rows=1 loops=1)

  • Join Filter: (("*SELECT* 1"._key)::text = ("*SELECT* 1_1"._key)::text)
  • Rows Removed by Join Filter: 276
4. 1.096 142.528 ↑ 1.0 1 1

Nested Loop (cost=0.68..780.24 rows=1 width=296) (actual time=90.837..142.528 rows=1 loops=1)

  • Join Filter: (("*SELECT* 1"._key)::text = x.asset_key)
  • Rows Removed by Join Filter: 268
5. 27.376 27.376 ↓ 269.0 269 1

Function Scan on jsonb_to_recordset x (cost=0.25..27.63 rows=1 width=32) (actual time=26.787..27.376 rows=269 loops=1)

  • Filter: ((asset_status_key = 'apm.5.available'::text) AND (_object_id = ANY ('{365,2306,2412,1315,1295,408,977,683,2381,2395,379,2380,677,1305,385,384,1310,2448,2434,404,2432,2491,2400,2405,389,184,1298,372,2444,125,1313,357,2403,1319,2373,78,2388,1302,376,121,981,397,687,2367,2371,396,1,2392,312,380,678,1306,2364,366,1311,2382,2409,392,407,693,2414,2396,2445,361,2433,1296,682,2313,2377,980,403,388,356,686,679,393,985,1297,2387,984,129,371,2370,128,1318,2,360,398,2368,2402,674,2408,2413,2391,745,1301,689,2376,375,694,402,2393,2398,391,382,1312,2442,672,979,406,681,2383,2415,81,377,362,649,2378,1307,387,367,2410,1293,2461,932,685,983,130,399,2365,2401,2369,394,986,2390,505,80,675,1304,680,374,2449,123,355,690,2397,2375,2407,2386,409,673,1300,370,127,1317,2545,359,2411,2379,978,386,2435,1316,363,931,671,1321,976,688,368,2443,2389,2406,390,401,2431,2394,2384,378,2399,1294,1308,383,684,1309,82,2374,2385,410,405,1303,373,1314,358,79,2366,400,2404,395,2430,982,1299,691,2372,369,1320,354,676,364}'::bigint[])))
  • Rows Removed by Filter: 8
6. 0.538 114.056 ↑ 2.0 1 269

Append (cost=0.42..752.59 rows=2 width=264) (actual time=0.072..0.424 rows=1 loops=269)

7. 0.538 43.040 ↑ 1.0 1 269

Subquery Scan on *SELECT* 1 (cost=0.42..548.03 rows=1 width=288) (actual time=0.071..0.160 rows=1 loops=269)

8. 42.502 42.502 ↑ 1.0 1 269

Index Scan using "Record_DAYPURPLE7595__object_id__package_id_idx" on "Record_DAYPURPLE7595" (cost=0.42..548.02 rows=1 width=612) (actual time=0.070..0.158 rows=1 loops=269)

  • Index Cond: ((_object_id)::bigint = 380)
  • Filter: ((NOT is_deleted) AND (_tenant_id IS NOT NULL) AND ('{"name": "RO-400978"}'::jsonb <@ (fields)::jsonb) AND ('{"serial_number": "1NPCXPEXXFD294671"}'::jsonb <@ (fields)::jsonb) AND ('{"manufacturer_name": "Peterbilt"}'::jsonb <@ (fields)::jsonb) AND ('{"model": "567"}'::jsonb <@ (fields)::jsonb) AND ('{"manufacture_year": 2015}'::jsonb <@ (fields)::jsonb))
  • Rows Removed by Filter: 276
9. 0.269 70.478 ↓ 0.0 0 269

Subquery Scan on *SELECT* 2 (cost=192.15..204.56 rows=1 width=240) (actual time=0.262..0.262 rows=0 loops=269)

10. 0.269 70.209 ↓ 0.0 0 269

Nested Loop (cost=192.15..204.55 rows=1 width=564) (actual time=0.261..0.261 rows=0 loops=269)

11. 0.538 69.940 ↓ 0.0 0 269

Bitmap Heap Scan on "Record_PACKAGE_RECORDS" (cost=192.00..196.06 rows=1 width=318) (actual time=0.260..0.260 rows=0 loops=269)

  • Recheck Cond: (('{"name": "RO-400978"}'::jsonb <@ (fields)::jsonb) AND ('{"serial_number": "1NPCXPEXXFD294671"}'::jsonb <@ (fields)::jsonb) AND ('{"manufacturer_name": "Peterbilt"}'::jsonb <@ (fields)::jsonb) AND ('{"model": "567"}'::jsonb <@ (fields)::jsonb) AND ('{"manufacture_year": 2015}'::jsonb <@ (fields)::jsonb))
  • Filter: ((NOT is_deleted) AND ((_object_id)::bigint = 380) AND ((_package_id)::bigint = ANY ('{249,5,42,125,157,46,1,248,160,229,252,41,45,161,44,159,7,130,241,162,255,48,250,40,158,75,47,163}'::bigint[])))
12. 69.402 69.402 ↓ 0.0 0 269

Bitmap Index Scan on "Record_PACKAGE_RECORDS_fields_idx" (cost=0.00..192.00 rows=1 width=0) (actual time=0.258..0.258 rows=0 loops=269)

  • Index Cond: (('{"name": "RO-400978"}'::jsonb <@ (fields)::jsonb) AND ('{"serial_number": "1NPCXPEXXFD294671"}'::jsonb <@ (fields)::jsonb) AND ('{"manufacturer_name": "Peterbilt"}'::jsonb <@ (fields)::jsonb) AND ('{"model": "567"}'::jsonb <@ (fields)::jsonb) AND ('{"manufacture_year": 2015}'::jsonb <@ (fields)::jsonb))
13. 0.000 0.000 ↓ 0.0 0

Index Only Scan using "Package_pkey" on "Package" p (cost=0.15..8.17 rows=1 width=8) (never executed)

  • Index Cond: (id = ("Record_PACKAGE_RECORDS"._package_id)::bigint)
  • Heap Fetches: 0
14. 0.230 1.256 ↑ 3.6 277 1

Append (cost=47.53..4,734.49 rows=1,001 width=90) (actual time=0.077..1.256 rows=277 loops=1)

15. 0.236 0.559 ↑ 2.3 277 1

Subquery Scan on *SELECT* 1_1 (cost=47.53..2,682.32 rows=631 width=108) (actual time=0.076..0.559 rows=277 loops=1)

16. 0.263 0.323 ↑ 2.3 277 1

Bitmap Heap Scan on "Record_DAYPURPLE7595" "Record_DAYPURPLE7595_1" (cost=47.53..2,676.01 rows=631 width=464) (actual time=0.075..0.323 rows=277 loops=1)

  • Recheck Cond: ((_object_id)::bigint = ANY ('{379,380,377,359,378}'::bigint[]))
  • Filter: ((NOT is_deleted) AND (_tenant_id IS NOT NULL))
  • Heap Blocks: exact=116
17. 0.060 0.060 ↑ 2.5 277 1

Bitmap Index Scan on "Record_DAYPURPLE7595__object_id__package_id_idx" (cost=0.00..47.38 rows=699 width=0) (actual time=0.060..0.060 rows=277 loops=1)

  • Index Cond: ((_object_id)::bigint = ANY ('{379,380,377,359,378}'::bigint[]))
18. 0.001 0.467 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=626.73..2,052.17 rows=370 width=60) (actual time=0.467..0.467 rows=0 loops=1)

19. 0.009 0.466 ↓ 0.0 0 1

Hash Join (cost=626.73..2,048.47 rows=370 width=416) (actual time=0.466..0.466 rows=0 loops=1)

  • Hash Cond: (("Record_PACKAGE_RECORDS_1"._package_id)::bigint = p_1.id)
20. 0.001 0.186 ↓ 0.0 0 1

Bitmap Heap Scan on "Record_PACKAGE_RECORDS" "Record_PACKAGE_RECORDS_1" (cost=615.79..2,036.54 rows=370 width=68) (actual time=0.186..0.186 rows=0 loops=1)

  • Recheck Cond: (((_object_id)::bigint = ANY ('{379,380,377,359,378}'::bigint[])) AND ((_package_id)::bigint = ANY ('{249,5,42,125,157,46,1,248,160,229,252,41,45,161,44,159,7,130,241,162,255,48,250,40,158,75,47,163}'::bigint[])))
  • Filter: (NOT is_deleted)
21. 0.185 0.185 ↓ 0.0 0 1

Bitmap Index Scan on "Record_PACKAGE_RECORDS__object_id__package_id_idx" (cost=0.00..615.70 rows=376 width=0) (actual time=0.184..0.185 rows=0 loops=1)

  • Index Cond: (((_object_id)::bigint = ANY ('{379,380,377,359,378}'::bigint[])) AND ((_package_id)::bigint = ANY ('{249,5,42,125,157,46,1,248,160,229,252,41,45,161,44,159,7,130,241,162,255,48,250,40,158,75,47,163}'::bigint[])))
22. 0.135 0.271 ↓ 1.0 267 1

Hash (cost=7.64..7.64 rows=264 width=8) (actual time=0.270..0.271 rows=267 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
23. 0.136 0.136 ↓ 1.0 267 1

Seq Scan on "Package" p_1 (cost=0.00..7.64 rows=264 width=8) (actual time=0.005..0.136 rows=267 loops=1)

24. 0.128 0.548 ↓ 65.5 131 1

Append (cost=0.42..648.72 rows=2 width=32) (actual time=0.091..0.548 rows=131 loops=1)

25. 0.115 0.420 ↓ 131.0 131 1

Subquery Scan on *SELECT* 1_2 (cost=0.42..546.37 rows=1 width=32) (actual time=0.090..0.420 rows=131 loops=1)

26. 0.305 0.305 ↓ 131.0 131 1

Index Scan using "Record_DAYPURPLE7595__object_id__package_id_idx" on "Record_DAYPURPLE7595" "Record_DAYPURPLE7595_2" (cost=0.42..546.36 rows=1 width=352) (actual time=0.089..0.305 rows=131 loops=1)

  • Index Cond: ((_object_id)::bigint = 2397)
  • Filter: ((NOT is_deleted) AND (_tenant_id IS NOT NULL) AND ('{"group__key": "group|4e4da8cc-4aec-4777-931b-884f8989f452"}'::jsonb <@ (fields)::jsonb))
  • Rows Removed by Filter: 72
27. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2_2 (cost=90.21..102.35 rows=1 width=32) (never executed)

28. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=90.21..102.34 rows=1 width=352) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on "Record_PACKAGE_RECORDS" "Record_PACKAGE_RECORDS_2" (cost=90.07..94.12 rows=1 width=242) (never executed)

  • Recheck Cond: (((_object_id)::bigint = 2397) AND ('{"group__key": "group|4e4da8cc-4aec-4777-931b-884f8989f452"}'::jsonb <@ (fields)::jsonb))
  • Filter: ((NOT is_deleted) AND ((_package_id)::bigint = ANY ('{249,5,42,125,157,46,1,248,160,229,252,41,45,161,44,159,7,130,241,162,255,48,250,40,158,75,47,163}'::bigint[])))
30. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=90.07..90.07 rows=1 width=0) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on "Record_PACKAGE_RECORDS__object_id__package_id_idx" (cost=0.00..21.25 rows=643 width=0) (never executed)

  • Index Cond: ((_object_id)::bigint = 2397)
32. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on "Record_PACKAGE_RECORDS_fields_idx" (cost=0.00..68.57 rows=609 width=0) (never executed)

  • Index Cond: ('{"group__key": "group|4e4da8cc-4aec-4777-931b-884f8989f452"}'::jsonb <@ (fields)::jsonb)
33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using "Package_pkey" on "Package" p_2 (cost=0.15..8.17 rows=1 width=8) (never executed)

  • Index Cond: (id = ("Record_PACKAGE_RECORDS_2"._package_id)::bigint)
  • Heap Fetches: 0
Planning time : 2.308 ms
Execution time : 144.796 ms