explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6l5E

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 36,363.006 ↓ 24.0 24 1

Subquery Scan on x2 (cost=2,950,521.78..2,950,521.81 rows=1 width=45) (actual time=36,362.615..36,363.006 rows=24 loops=1)

2. 0.287 36,363.002 ↓ 24.0 24 1

Unique (cost=2,950,521.78..2,950,521.80 rows=1 width=46) (actual time=36,362.613..36,363.002 rows=24 loops=1)

3. 1.990 36,362.715 ↓ 3,263.0 3,263 1

Sort (cost=2,950,521.78..2,950,521.79 rows=1 width=46) (actual time=36,362.613..36,362.715 rows=3,263 loops=1)

  • Sort Key: x7.name, x7.uuid, ((x8.x9 IS NOT NULL))
  • Sort Method: quicksort Memory: 351kB
4. 198.542 36,360.725 ↓ 3,263.0 3,263 1

Nested Loop (cost=2,842,977.20..2,950,521.77 rows=1 width=46) (actual time=33,019.660..36,360.725 rows=3,263 loops=1)

  • Join Filter: (x8.x16 = x7.number_code)
  • Rows Removed by Join Filter: 2877349
5. 1.288 36,011.947 ↓ 3,266.0 3,266 1

Nested Loop (cost=2,842,977.20..2,950,491.93 rows=1 width=8) (actual time=33,019.520..36,011.947 rows=3,266 loops=1)

6. 3.562 36,007.393 ↓ 3,266.0 3,266 1

Hash Left Join (cost=2,842,977.06..2,950,491.77 rows=1 width=24) (actual time=33,019.497..36,007.393 rows=3,266 loops=1)

  • Hash Cond: ((x8.x15 = x27.drt_count) AND (x8.x13 = x27.meter_serial) AND (x8.x14 = x27.gateway_id))
  • Filter: (x27.uuid IS NULL)
  • Rows Removed by Filter: 4873
7. 1.088 36,001.804 ↑ 2.9 8,139 1

Subquery Scan on x8 (cost=2,842,681.53..2,950,008.42 rows=23,850 width=32) (actual time=33,014.382..36,001.804 rows=8,139 loops=1)

  • Filter: (x8.x9 IS NULL)
  • Rows Removed by Filter: 572
8. 840.312 36,000.716 ↑ 547.6 8,711 1

Unique (cost=2,842,681.53..2,902,307.58 rows=4,770,084 width=132) (actual time=33,014.380..36,000.716 rows=8,711 loops=1)

9. 9,566.983 35,160.404 ↓ 1.9 9,196,744 1

Sort (cost=2,842,681.53..2,854,606.74 rows=4,770,084 width=132) (actual time=33,014.379..35,160.404 rows=9,196,744 loops=1)

  • Sort Key: x11.meter_serial, x12.gateway_id, x11.drt_count, x11.wireless
  • Sort Method: external merge Disk: 451032kB
10. 1,639.057 25,593.421 ↓ 1.9 9,196,744 1

Hash Join (cost=23,773.64..1,661,384.05 rows=4,770,084 width=132) (actual time=117.206..25,593.421 rows=9,196,744 loops=1)

  • Hash Cond: (x12.gateway_id = x20.uuid)
11. 3,620.942 23,954.320 ↓ 1.9 9,196,744 1

Hash Join (cost=23,768.81..1,648,202.61 rows=4,770,084 width=36) (actual time=117.147..23,954.320 rows=9,196,744 loops=1)

  • Hash Cond: (x11.file_id = x12.uuid)
12. 20,217.138 20,217.138 ↓ 1.9 9,196,744 1

Seq Scan on frames x11 (cost=0.00..1,535,632.11 rows=4,770,084 width=36) (actual time=0.025..20,217.138 rows=9,196,744 loops=1)

  • Filter: COALESCE(CASE WHEN (parse_error_type IS NULL) THEN NULL::boolean ELSE (parse_error_type <> 2) END, true)
  • Rows Removed by Filter: 386288
13. 45.705 116.240 ↑ 1.0 255,414 1

Hash (cost=18,830.14..18,830.14 rows=255,414 width=32) (actual time=116.240..116.240 rows=255,414 loops=1)

  • Buckets: 65536 Batches: 8 Memory Usage: 2539kB
14. 70.535 70.535 ↑ 1.0 255,414 1

Seq Scan on files x12 (cost=0.00..18,830.14 rows=255,414 width=32) (actual time=0.046..70.535 rows=255,414 loops=1)

15. 0.014 0.044 ↑ 1.0 81 1

Hash (cost=3.81..3.81 rows=81 width=16) (actual time=0.044..0.044 rows=81 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
16. 0.030 0.030 ↑ 1.0 81 1

Seq Scan on gateways x20 (cost=0.00..3.81 rows=81 width=16) (actual time=0.011..0.030 rows=81 loops=1)

17. 1.138 2.027 ↑ 1.0 7,801 1

Hash (cost=159.01..159.01 rows=7,801 width=40) (actual time=2.027..2.027 rows=7,801 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 613kB
18. 0.889 0.889 ↑ 1.0 7,801 1

Seq Scan on meter_templates x27 (cost=0.00..159.01 rows=7,801 width=40) (actual time=0.011..0.889 rows=7,801 loops=1)

19. 3.266 3.266 ↑ 1.0 1 3,266

Index Only Scan using gateways_pkey on gateways x26 (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=3,266)

  • Index Cond: (uuid = x8.x14)
  • Heap Fetches: 3266
20. 150.236 150.236 ↑ 1.0 882 3,266

Seq Scan on manufacturers x7 (cost=0.00..18.82 rows=882 width=45) (actual time=0.001..0.046 rows=882 loops=3,266)

Planning time : 1.782 ms
Execution time : 36,406.872 ms