explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KOO

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 187,962.688 ↓ 24.0 24 1

Subquery Scan on x2 (cost=1,580,826.59..1,580,826.61 rows=1 width=45) (actual time=187,340.917..187,962.688 rows=24 loops=1)

2. 181.469 187,962.680 ↓ 24.0 24 1

Unique (cost=1,580,826.59..1,580,826.60 rows=1 width=46) (actual time=187,340.914..187,962.680 rows=24 loops=1)

3. 1,378.513 187,781.211 ↓ 1,534,995.0 1,534,995 1

Sort (cost=1,580,826.59..1,580,826.59 rows=1 width=46) (actual time=187,340.913..187,781.211 rows=1,534,995 loops=1)

  • Sort Key: x7.name, x7.uuid, x7.code, x7.number_code, ((x8.parse_error_type IS NOT NULL))
  • Sort Method: external merge Disk: 75240kB
4. 93,207.973 186,402.698 ↓ 1,534,995.0 1,534,995 1

Nested Loop (cost=20,698.85..1,580,826.58 rows=1 width=46) (actual time=22,373.997..186,402.698 rows=1,534,995 loops=1)

  • Join Filter: (x8.manufacture_id = x7.number_code)
  • Rows Removed by Join Filter: 1352349999
5. 0.000 22,583.943 ↓ 1,535,017.0 1,535,017 1

Gather (cost=20,698.85..1,580,796.73 rows=1 width=8) (actual time=22,373.960..22,583.943 rows=1,535,017 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 592.893 24,206.279 ↓ 511,672.0 511,672 3

Hash Left Join (cost=19,698.85..1,579,796.63 rows=1 width=8) (actual time=22,367.683..24,206.279 rows=511,672 loops=3)

  • Hash Cond: ((x8.meter_serial = x12.meter_serial) AND (x11.uuid = x12.gateway_id) AND (x8.drt_count = x12.drt_count))
  • Filter: (x12.uuid IS NULL)
  • Rows Removed by Filter: 2466613
7. 454.176 23,611.073 ↑ 1.2 2,978,285 3

Hash Join (cost=19,403.32..1,552,221.81 rows=3,464,035 width=32) (actual time=22,365.256..23,611.073 rows=2,978,285 loops=3)

  • Hash Cond: (x10.gateway_id = x11.uuid)
8. 1,424.068 23,156.852 ↑ 1.2 2,978,285 3

Parallel Hash Join (cost=19,398.50..1,542,648.13 rows=3,464,035 width=32) (actual time=22,365.124..23,156.852 rows=2,978,285 loops=3)

  • Hash Cond: (x8.file_id = x10.uuid)
9. 21,667.308 21,667.308 ↑ 1.2 2,978,285 3

Parallel Seq Scan on frames x8 (cost=0.00..1,466,068.38 rows=3,464,035 width=32) (actual time=0.021..21,667.308 rows=2,978,285 loops=3)

  • Filter: ((parse_error_type IS NULL) AND ((parse_error_type IS NULL) OR (parse_error_type <> 2)))
  • Rows Removed by Filter: 216059
10. 22.834 65.476 ↑ 1.2 85,138 3

Parallel Hash (cost=17,340.22..17,340.22 rows=106,422 width=32) (actual time=65.476..65.476 rows=85,138 loops=3)

  • Buckets: 65536 Batches: 8 Memory Usage: 2592kB
11. 42.642 42.642 ↑ 1.2 85,138 3

Parallel Seq Scan on files x10 (cost=0.00..17,340.22 rows=106,422 width=32) (actual time=0.026..42.642 rows=85,138 loops=3)

12. 0.016 0.045 ↑ 1.0 81 3

Hash (cost=3.81..3.81 rows=81 width=16) (actual time=0.045..0.045 rows=81 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
13. 0.029 0.029 ↑ 1.0 81 3

Seq Scan on gateways x11 (cost=0.00..3.81 rows=81 width=16) (actual time=0.008..0.029 rows=81 loops=3)

14. 1.291 2.313 ↑ 1.0 7,801 3

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

  • Buckets: 8192 Batches: 1 Memory Usage: 613kB
15. 1.022 1.022 ↑ 1.0 7,801 3

Seq Scan on meter_templates x12 (cost=0.00..159.01 rows=7,801 width=40) (actual time=0.016..1.022 rows=7,801 loops=3)

16. 70,610.782 70,610.782 ↑ 1.0 882 1,535,017

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

Planning time : 1.288 ms
Execution time : 188,000.374 ms