explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LDpo

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 0.372 ↓ 0.0 0 1

Insert on target_highpriority (cost=3,760.86..3,831.69 rows=1 width=122) (actual time=0.372..0.372 rows=0 loops=1)

2.          

CTE inserts

3. 0.002 674.021 ↓ 0.0 0 1

Insert on target (cost=3,654.37..3,714.61 rows=2,008 width=140) (actual time=674.021..674.021 rows=0 loops=1)

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

Subquery Scan on *SELECT* (cost=3,654.37..3,714.61 rows=2,008 width=140) (actual time=674.019..674.019 rows=0 loops=1)

5. 0.008 674.018 ↓ 0.0 0 1

HashAggregate (cost=3,654.37..3,674.45 rows=2,008 width=88) (actual time=674.018..674.018 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.002 674.010 ↓ 0.0 0 1

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

7. 3.940 673.591 ↓ 0.0 0 1

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

8. 0.654 0.654 ↑ 1.0 2,009 1

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

9. 668.997 668.997 ↑ 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.333..0.333 rows=1 loops=2,009)

  • Index Cond: ((collector_id = t_1.collector_id) AND (scantype_id = t_1.scantype_id) AND (iprange(t_1.cidrval) <<= iprange(cidrval)))
10. 0.001 0.417 ↓ 0.0 0 1

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

11. 0.416 0.416 ↓ 0.0 0 1

Seq Scan on targets t_2 (cost=0.00..46.09 rows=1 width=66) (actual time=0.416..0.416 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.000 0.369 ↓ 0.0 0 1

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

14. 0.013 0.369 ↓ 0.0 0 1

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

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

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

Seq Scan on targets t (cost=0.00..46.09 rows=1 width=66) (actual time=0.355..0.355 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 : 2.212 ms
Execution time : 674.843 ms