explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 177k

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 0.647 ↓ 0.0 0 1

Insert on target_highpriority (cost=12,452.72..12,523.54 rows=1 width=122) (actual time=0.647..0.647 rows=0 loops=1)

2.          

CTE updates

3. 0.003 991.235 ↓ 0.0 0 1

Update on target zt_1 (cost=0.43..8,672.28 rows=363 width=130) (actual time=991.235..991.235 rows=0 loops=1)

4. 3.143 991.232 ↓ 0.0 0 1

Nested Loop (cost=0.43..8,672.28 rows=363 width=130) (actual time=991.232..991.232 rows=0 loops=1)

5. 1.670 1.670 ↑ 1.0 2,009 1

Seq Scan on targets st (cost=0.00..46.09 rows=2,009 width=56) (actual time=0.009..1.670 rows=2,009 loops=1)

6. 986.419 986.419 ↓ 0.0 0 2,009

Index Scan using idx_target_colcidr on target zt_1 (cost=0.43..4.28 rows=1 width=114) (actual time=0.491..0.491 rows=0 loops=2,009)

  • Index Cond: ((collector_id = st.collector_id) AND ((cidrval)::inet = (st.cidrval)::inet))
  • Filter: ((st.config <> details) AND (st.scantype_id = scantype_id))
  • Rows Removed by Filter: 2
7.          

CTE inserts

8. 0.003 1,564.323 ↓ 0.0 0 1

Insert on target (cost=3,673.95..3,734.19 rows=2,008 width=140) (actual time=1,564.323..1,564.323 rows=0 loops=1)

  • Conflict Resolution: NOTHING
  • Conflict Arbiter Indexes: uk_target
  • Tuples Inserted: 0
  • Conflicting Tuples: 0
9. 0.002 1,564.320 ↓ 0.0 0 1

Subquery Scan on *SELECT* (cost=3,673.95..3,734.19 rows=2,008 width=140) (actual time=1,564.320..1,564.320 rows=0 loops=1)

10. 0.019 1,564.318 ↓ 0.0 0 1

HashAggregate (cost=3,673.95..3,694.03 rows=2,008 width=88) (actual time=1,564.318..1,564.318 rows=0 loops=1)

  • Group Key: t_1.collector_id, t_1.zone_id, t_1.cidrval, t_1.device_id, t_1.cidr_id, t_1.route_id, t_1.scantype_id, t_1.config
11. 0.001 1,564.299 ↓ 0.0 0 1

Append (cost=0.42..3,633.79 rows=2,008 width=88) (actual time=1,564.299..1,564.299 rows=0 loops=1)

12. 0.001 1,563.786 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.42..3,564.78 rows=2,007 width=66) (actual time=1,563.786..1,563.786 rows=0 loops=1)

  • Join Filter: false
13. 13.905 1,563.785 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.42..3,544.71 rows=2,007 width=66) (actual time=1,563.785..1,563.785 rows=0 loops=1)

14. 0.941 0.941 ↑ 1.0 2,009 1

Seq Scan on targets t_1 (cost=0.00..46.09 rows=2,009 width=66) (actual time=0.007..0.941 rows=2,009 loops=1)

15. 1,548.939 1,548.939 ↑ 32.0 1 2,009

Index Scan using idx_target_colscancidr_gist on target zt_2 (cost=0.42..42.83 rows=32 width=15) (actual time=0.771..0.771 rows=1 loops=2,009)

  • Index Cond: ((collector_id = t_1.collector_id) AND (scantype_id = t_1.scantype_id) AND (iprange(cidrval) >>= iprange(t_1.cidrval)))
16. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.00 rows=0 width=0) (never executed)

  • One-Time Filter: false
17. 0.001 0.512 ↓ 0.0 0 1

Nested Loop Anti Join (cost=0.42..48.93 rows=1 width=66) (actual time=0.512..0.512 rows=0 loops=1)

18. 0.001 0.511 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.00..46.10 rows=1 width=66) (actual time=0.511..0.511 rows=0 loops=1)

  • Join Filter: false
19. 0.510 0.510 ↓ 0.0 0 1

Seq Scan on targets t_2 (cost=0.00..46.09 rows=1 width=66) (actual time=0.510..0.510 rows=0 loops=1)

  • Filter: overwrite
  • Rows Removed by Filter: 2009
20. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.00 rows=0 width=0) (never executed)

  • One-Time Filter: false
21. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_target_colscancidr_gist on target zt_3 (cost=0.42..332.06 rows=162 width=15) (never executed)

  • Index Cond: ((collector_id = t_2.collector_id) AND (scantype_id = t_2.scantype_id) AND (iprange(cidrval) = iprange(t_2.cidrval)))
22. 0.001 0.640 ↓ 0.0 0 1

Nested Loop Anti Join (cost=46.25..117.08 rows=1 width=122) (actual time=0.640..0.640 rows=0 loops=1)

23. 0.030 0.639 ↓ 0.0 0 1

Hash Right Join (cost=46.11..108.87 rows=1 width=70) (actual time=0.639..0.639 rows=0 loops=1)

  • Hash Cond: ((tparent.collector_id = t.collector_id) AND (tparent.scantype_id = t.scantype_id) AND ((tparent.cidrval)::inet = (t.cidrval)::inet))
24. 0.000 0.000 ↓ 0.0 0

CTE Scan on inserts tparent (cost=0.00..40.16 rows=2,008 width=44) (never executed)

25. 0.001 0.609 ↓ 0.0 0 1

Hash (cost=46.09..46.09 rows=1 width=66) (actual time=0.609..0.609 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
26. 0.608 0.608 ↓ 0.0 0 1

Seq Scan on targets t (cost=0.00..46.09 rows=1 width=66) (actual time=0.608..0.608 rows=0 loops=1)

  • Filter: (priority IS NOT NULL)
  • Rows Removed by Filter: 2009
27. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_target_hp_colscancidr_gist on target_highpriority zt (cost=0.14..4.17 rows=1 width=40) (never executed)

  • Index Cond: ((collector_id = t.collector_id) AND (scantype_id = t.scantype_id))
  • Filter: ((cidrval)::inet = (t.cidrval)::inet)
Planning time : 49.641 ms
Execution time : 2,576.996 ms