explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NdoX

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 0.762 ↓ 0.0 0 1

Insert on target_highpriority (cost=3,760.42..3,831.25 rows=1 width=122) (actual time=0.762..0.762 rows=0 loops=1)

2.          

CTE inserts

3. 0.003 422.967 ↓ 0.0 0 1

Insert on target (cost=3,653.93..3,714.17 rows=2,008 width=140) (actual time=422.967..422.967 rows=0 loops=1)

  • Conflict Resolution: NOTHING
  • Conflict Arbiter Indexes: uk_target
  • Tuples Inserted: 0
  • Conflicting Tuples: 0
4. 0.002 422.964 ↓ 0.0 0 1

Subquery Scan on *SELECT* (cost=3,653.93..3,714.17 rows=2,008 width=140) (actual time=422.964..422.964 rows=0 loops=1)

5. 0.015 422.962 ↓ 0.0 0 1

HashAggregate (cost=3,653.93..3,674.01 rows=2,008 width=88) (actual time=422.962..422.962 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
6. 0.001 422.947 ↓ 0.0 0 1

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

7. 2.260 422.657 ↓ 0.0 0 1

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

8. 0.516 0.516 ↑ 1.0 2,009 1

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

9. 419.881 419.881 ↑ 32.0 1 2,009

Index Scan using idx_target_colscancidr_gist on target zt_1 (cost=0.42..42.83 rows=32 width=15) (actual time=0.209..0.209 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)))
10. 0.001 0.289 ↓ 0.0 0 1

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

11. 0.288 0.288 ↓ 0.0 0 1

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

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

Index Scan using idx_target_colscancidr_gist on target zt_2 (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)))
13. 0.001 0.756 ↓ 0.0 0 1

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

14. 0.038 0.755 ↓ 0.0 0 1

Hash Right Join (cost=46.11..108.87 rows=1 width=70) (actual time=0.755..0.755 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))
15. 0.000 0.000 ↓ 0.0 0

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

16. 0.000 0.717 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
17. 0.717 0.717 ↓ 0.0 0 1

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

  • Filter: (priority IS NOT NULL)
  • Rows Removed by Filter: 2009
18. 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 : 5.776 ms
Execution time : 460.926 ms