explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DDlM

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 83.088 ↑ 234.3 23 1

Hash Join (cost=118,213.58..236,581.29 rows=5,390 width=16) (actual time=63.928..83.088 rows=23 loops=1)

  • Hash Cond: (rtpezoav_1.zo_id = rtpezoav.zo_id)
2. 4.193 48.189 ↑ 63.9 23 1

Hash Semi Join (cost=0.72..118,176.80 rows=1,469 width=20) (actual time=29.033..48.189 rows=23 loops=1)

  • Hash Cond: (rtpezoav_1.zo_id = (unnest($8)))
3. 3.410 43.980 ↓ 1.9 54,883 1

Append (cost=0.43..118,083.07 rows=29,372 width=16) (actual time=0.031..43.980 rows=54,883 loops=1)

4. 40.561 40.561 ↓ 1.9 54,883 1

Index Scan using rtpezoav_unique on rtpezoav rtpezoav_1 (cost=0.43..56,206.07 rows=28,327 width=16) (actual time=0.030..40.561 rows=54,883 loops=1)

  • Index Cond: ((pe_id = $7) AND (ad_id = $6))
5. 0.001 0.009 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=1.00..61,730.14 rows=1,045 width=16) (actual time=0.009..0.009 rows=0 loops=1)

6. 0.000 0.008 ↓ 0.0 0 1

Nested Loop Anti Join (cost=1.00..61,719.69 rows=1,045 width=56) (actual time=0.008..0.008 rows=0 loops=1)

  • Join Filter: (av_1.pe_id = rtpe.id)
7. 0.002 0.008 ↓ 0.0 0 1

Nested Loop (cost=0.57..56,497.49 rows=2,361 width=20) (actual time=0.008..0.008 rows=0 loops=1)

8. 0.005 0.005 ↑ 1.0 1 1

Index Only Scan using rtpe_id_baseline_id_idx on rtpe (cost=0.14..8.15 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (id = $7)
  • Heap Fetches: 1
9. 0.001 0.001 ↓ 0.0 0 1

Index Scan using rtpezoav_unique on rtpezoav av (cost=0.43..56,206.07 rows=28,327 width=20) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((pe_id = rtpe.baseline_id) AND (ad_id = $6))
10. 0.000 0.000 ↓ 0.0 0

Index Only Scan using rtpezoav_unique on rtpezoav av_1 (cost=0.43..2.20 rows=1 width=12) (never executed)

  • Index Cond: ((pe_id = $7) AND (zo_id = av.zo_id) AND (ad_id = av.ad_id) AND (ad_id = $6))
  • Heap Fetches: 0
11. 0.009 0.016 ↓ 2.3 23 1

Hash (cost=0.17..0.17 rows=10 width=4) (actual time=0.015..0.016 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 0.006 0.007 ↓ 2.3 23 1

ProjectSet (cost=0.00..0.07 rows=10 width=4) (actual time=0.003..0.007 rows=23 loops=1)

13. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

14. 0.005 34.886 ↑ 31.9 23 1

Hash (cost=118,203.68..118,203.68 rows=734 width=20) (actual time=34.886..34.886 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
15. 0.011 34.881 ↑ 31.9 23 1

Hash Semi Join (cost=0.81..118,203.68 rows=734 width=20) (actual time=14.518..34.881 rows=23 loops=1)

  • Hash Cond: (rtpezoav.zo_id = (unnest($8)))
16. 0.002 34.861 ↑ 638.5 23 1

Append (cost=0.52..118,156.68 rows=14,685 width=16) (actual time=14.504..34.861 rows=23 loops=1)

17. 34.843 34.847 ↑ 615.8 23 1

Index Scan using rtpezoav_unique on rtpezoav (cost=0.52..56,347.79 rows=14,163 width=16) (actual time=14.504..34.847 rows=23 loops=1)

  • Index Cond: ((pe_id = $7) AND (ad_id = $5))
  • Filter: CASE WHEN ($2 ? 'vz_ids'::text) THEN (hashed SubPlan 1) ELSE true END
  • Rows Removed by Filter: 54860
18.          

SubPlan (for Index Scan)

19. 0.004 0.004 ↓ 2.3 23 1

ProjectSet (cost=0.00..0.07 rows=10 width=4) (actual time=0.001..0.004 rows=23 loops=1)

20. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

21. 0.001 0.012 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=1.09..61,735.46 rows=522 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Filter: CASE WHEN ($2 ? 'vz_ids'::text) THEN (hashed SubPlan 1) ELSE true END
22. 0.000 0.011 ↓ 0.0 0 1

Nested Loop Anti Join (cost=1.00..61,719.69 rows=1,045 width=56) (actual time=0.011..0.011 rows=0 loops=1)

  • Join Filter: (av_1_1.pe_id = rtpe_1.id)
23. 0.001 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.57..56,497.49 rows=2,361 width=20) (actual time=0.011..0.011 rows=0 loops=1)

24. 0.009 0.009 ↑ 1.0 1 1

Index Only Scan using rtpe_id_baseline_id_idx on rtpe rtpe_1 (cost=0.14..8.15 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (id = $7)
  • Heap Fetches: 1
25. 0.001 0.001 ↓ 0.0 0 1

Index Scan using rtpezoav_unique on rtpezoav av_2 (cost=0.43..56,206.07 rows=28,327 width=20) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((pe_id = rtpe_1.baseline_id) AND (ad_id = $5))
26. 0.000 0.000 ↓ 0.0 0

Index Only Scan using rtpezoav_unique on rtpezoav av_1_1 (cost=0.43..2.20 rows=1 width=12) (never executed)

  • Index Cond: ((pe_id = $7) AND (zo_id = av_2.zo_id) AND (ad_id = av_2.ad_id) AND (ad_id = $5))
  • Heap Fetches: 0
27. 0.003 0.009 ↓ 2.3 23 1

Hash (cost=0.17..0.17 rows=10 width=4) (actual time=0.009..0.009 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.005 0.006 ↓ 2.3 23 1

ProjectSet (cost=0.00..0.07 rows=10 width=4) (actual time=0.003..0.006 rows=23 loops=1)

29. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)