explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JGcg : Brian's Asset Query

Settings
# exclusive inclusive rows x rows loops node
1. 0.058 6,106.186 ↑ 1.0 1 1

Sort (cost=3,929.98..3,929.98 rows=1 width=264) (actual time=6,106.185..6,106.186 rows=1 loops=1)

  • Sort Key: "*SELECT* 1".name
  • Sort Method: quicksort Memory: 25kB
2. 0.095 6,106.128 ↑ 1.0 1 1

Nested Loop Semi Join (cost=2,065.10..3,929.97 rows=1 width=264) (actual time=3,565.095..6,106.128 rows=1 loops=1)

  • Join Filter: (("*SELECT* 1"._key)::text = "*SELECT* 1_2".asset__key)
  • Rows Removed by Join Filter: 130
3. 0.139 6,080.546 ↑ 1.0 1 1

Nested Loop (cost=2,064.68..3,281.22 rows=1 width=386) (actual time=3,539.514..6,080.546 rows=1 loops=1)

  • Join Filter: (("*SELECT* 1"._key)::text = ("*SELECT* 1_1"._key)::text)
  • Rows Removed by Join Filter: 276
4. 2.331 6,078.069 ↑ 1.0 1 1

Nested Loop (cost=2,064.25..2,300.52 rows=1 width=264) (actual time=3,538.168..6,078.069 rows=1 loops=1)

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

Function Scan on jsonb_to_recordset x (cost=0.25..27.63 rows=1 width=32) (actual time=79.969..80.804 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 5,994.934 ↑ 2.0 1 269

Append (cost=2,064.00..2,272.86 rows=2 width=232) (actual time=21.930..22.286 rows=1 loops=269)

7. 0.807 5,899.439 ↑ 1.0 1 269

Subquery Scan on *SELECT* 1 (cost=2,064.00..2,068.30 rows=1 width=256) (actual time=21.929..21.931 rows=1 loops=269)

8. 7.263 5,898.632 ↑ 1.0 1 269

Bitmap Heap Scan on "Record_DAYPURPLE7595" (cost=2,064.00..2,068.29 rows=1 width=464) (actual time=21.927..21.928 rows=1 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 (_tenant_id IS NOT NULL) AND ((_object_id)::bigint = ANY ('{379,380,377,359,378}'::bigint[])))
  • Heap Blocks: exact=269
9. 5,891.369 5,891.369 ↑ 1.0 1 269

Bitmap Index Scan on "Record_DAYPURPLE7595_fields_idx" (cost=0.00..2,064.00 rows=1 width=0) (actual time=21.901..21.901 rows=1 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))
10. 0.269 94.957 ↓ 0.0 0 269

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

11. 0.269 94.688 ↓ 0.0 0 269

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

12. 0.538 94.419 ↓ 0.0 0 269

Bitmap Heap Scan on "Record_PACKAGE_RECORDS" (cost=192.00..196.06 rows=1 width=318) (actual time=0.351..0.351 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 = 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[])))
13. 93.881 93.881 ↓ 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.349..0.349 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))
14. 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
15. 0.234 2.338 ↓ 1.4 277 1

Append (cost=0.42..978.21 rows=200 width=122) (actual time=0.067..2.338 rows=277 loops=1)

16. 0.244 1.987 ↓ 2.2 277 1

Subquery Scan on *SELECT* 1_1 (cost=0.42..547.58 rows=126 width=140) (actual time=0.066..1.987 rows=277 loops=1)

17. 1.743 1.743 ↓ 2.2 277 1

Index Scan using "Record_DAYPURPLE7595__object_id__package_id_idx" on "Record_DAYPURPLE7595" "Record_DAYPURPLE7595_1" (cost=0.42..546.32 rows=126 width=612) (actual time=0.064..1.743 rows=277 loops=1)

  • Index Cond: ((_object_id)::bigint = 380)
  • Filter: ((NOT is_deleted) AND (_tenant_id IS NOT NULL))
18. 0.002 0.117 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=11.37..430.63 rows=74 width=92) (actual time=0.116..0.117 rows=0 loops=1)

19. 0.002 0.115 ↓ 0.0 0 1

Hash Join (cost=11.37..429.89 rows=74 width=564) (actual time=0.115..0.115 rows=0 loops=1)

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

Index Scan using "Record_PACKAGE_RECORDS__object_id__package_id_idx" on "Record_PACKAGE_RECORDS" "Record_PACKAGE_RECORDS_1" (cost=0.42..418.57 rows=74 width=302) (actual time=0.113..0.113 rows=0 loops=1)

  • Index Cond: (((_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[])))
  • Filter: (NOT is_deleted)
21. 0.000 0.000 ↓ 0.0 0

Hash (cost=7.64..7.64 rows=264 width=8) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Seq Scan on "Package" p_1 (cost=0.00..7.64 rows=264 width=8) (never executed)

23. 0.112 25.487 ↓ 65.5 131 1

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

24. 0.121 25.375 ↓ 131.0 131 1

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

25. 25.254 25.254 ↓ 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=12.245..25.254 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
26. 0.000 0.000 ↓ 0.0 0

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

27. 0.000 0.000 ↓ 0.0 0

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

28. 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[])))
29. 0.000 0.000 ↓ 0.0 0

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

30. 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)
31. 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)
32. 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 : 6.785 ms
Execution time : 6,108.054 ms