explain.depesz.com

PostgreSQL's explain analyze made readable

Result: avA7

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 114.235 ↑ 1.0 1 1

Aggregate (cost=165,692.33..165,692.34 rows=1 width=8) (actual time=114.235..114.235 rows=1 loops=1)

2. 1.007 114.228 ↑ 76,845.2 23 1

Merge Join (cost=125,892.40..152,436.54 rows=1,767,439 width=16) (actual time=114.219..114.228 rows=23 loops=1)

  • Merge Cond: (rtpezoav.zo_id = rtpezoav_1.zo_id)
3. 0.010 65.102 ↑ 284.5 23 1

Sort (cost=61,291.66..61,308.02 rows=6,543 width=20) (actual time=65.100..65.102 rows=23 loops=1)

  • Sort Key: rtpezoav.zo_id
  • Sort Method: quicksort Memory: 26kB
4. 3.817 65.092 ↑ 284.5 23 1

Hash Semi Join (cost=1,002.39..60,876.98 rows=6,543 width=20) (actual time=50.301..65.092 rows=23 loops=1)

  • Hash Cond: (rtpezoav.zo_id = (unnest('{354024,354040,354041,354042,354043,354056,354062,354063,354064,354065,354038,354039,354036,354066,354035,354034,354033,354031,354030,354029,354028,354027,354026}'::integer[])))
5. 15.231 61.258 ↑ 1.0 54,883 1

Gather (cost=1,001.74..60,654.18 rows=56,896 width=16) (actual time=16.780..61.258 rows=54,883 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 0.957 46.027 ↑ 1.3 18,294 3 / 3

Parallel Append (cost=1.74..53,964.58 rows=23,707 width=16) (actual time=6.426..46.027 rows=18,294 loops=3)

7. 41.813 42.078 ↑ 1.3 18,294 3 / 3

Parallel Bitmap Heap Scan on rtpezoav (cost=28.34..23,449.76 rows=23,706 width=16) (actual time=3.428..42.078 rows=18,294 loops=3)

  • Recheck Cond: (pe_id = 4)
  • Rows Removed by Index Recheck: 21674
  • Filter: (ad_id = 1096)
  • Rows Removed by Filter: 353513
  • Heap Blocks: lossy=2852
8. 0.265 0.265 ↑ 13.2 87,040 1 / 3

Bitmap Index Scan on rtpezoav_pe_id_idx (cost=0.00..14.12 rows=1,152,707 width=0) (actual time=0.795..0.795 rows=87,040 loops=1)

  • Index Cond: (pe_id = 4)
9. 0.000 2.992 ↓ 0.0 0 1 / 3

Subquery Scan on *SELECT* 2 (cost=1.74..30,396.29 rows=1 width=16) (actual time=8.976..8.976 rows=0 loops=1)

10. 0.000 2.992 ↓ 0.0 0 1 / 3

Nested Loop Anti Join (cost=1.74..30,396.28 rows=1 width=56) (actual time=8.976..8.976 rows=0 loops=1)

  • Join Filter: (av_1.pe_id = rtpe.id)
11. 0.004 2.992 ↓ 0.0 0 1 / 3

Hash Join (cost=1.31..27,870.61 rows=1,951 width=20) (actual time=8.974..8.975 rows=0 loops=1)

  • Hash Cond: (av.pe_id = rtpe.baseline_id)
12. 2.982 2.982 ↑ 46,828.0 1 1 / 3

Parallel Seq Scan on rtpezoav av (cost=0.00..27,724.67 rows=46,828 width=20) (actual time=8.946..8.946 rows=1 loops=1)

  • Filter: (ad_id = 1096)
  • Rows Removed by Filter: 43536
13. 0.000 0.006 ↓ 0.0 0 1 / 3

Hash (cost=1.30..1.30 rows=1 width=8) (actual time=0.018..0.018 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
14. 0.006 0.006 ↑ 1.0 1 1 / 3

Seq Scan on rtpe (cost=0.00..1.30 rows=1 width=8) (actual time=0.014..0.017 rows=1 loops=1)

  • Filter: (id = 4)
  • Rows Removed by Filter: 23
15. 0.000 0.000 ↓ 0.0 0 / 3

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

  • Index Cond: ((pe_id = 4) AND (zo_id = av.zo_id) AND (ad_id = av.ad_id) AND (ad_id = 1096))
  • Heap Fetches: 0
16. 0.008 0.017 ↑ 1.0 23 1

Hash (cost=0.36..0.36 rows=23 width=4) (actual time=0.017..0.017 rows=23 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.008 0.009 ↑ 1.0 23 1

ProjectSet (cost=0.00..0.13 rows=23 width=4) (actual time=0.004..0.009 rows=23 loops=1)

18. 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)

19. 7.277 48.119 ↑ 2.2 24,870 1

Sort (cost=64,600.74..64,735.80 rows=54,025 width=16) (actual time=47.418..48.119 rows=24,870 loops=1)

  • Sort Key: rtpezoav_1.zo_id
  • Sort Method: quicksort Memory: 4109kB
20. 6.496 40.842 ↓ 1.0 54,883 1

Gather (cost=1,001.74..60,354.01 rows=54,025 width=16) (actual time=0.558..40.842 rows=54,883 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
21. 0.623 34.346 ↑ 1.2 18,294 3 / 3

Parallel Append (cost=1.74..53,951.51 rows=22,511 width=16) (actual time=0.115..34.346 rows=18,294 loops=3)

22. 33.628 33.717 ↑ 1.2 18,294 3 / 3

Parallel Bitmap Heap Scan on rtpezoav rtpezoav_1 (cost=27.63..23,449.04 rows=22,510 width=16) (actual time=0.107..33.717 rows=18,294 loops=3)

  • Recheck Cond: (pe_id = 4)
  • Rows Removed by Index Recheck: 21674
  • Filter: (ad_id = 1072)
  • Rows Removed by Filter: 353513
  • Heap Blocks: lossy=2883
23. 0.089 0.089 ↑ 13.2 87,040 1 / 3

Bitmap Index Scan on rtpezoav_pe_id_idx (cost=0.00..14.12 rows=1,152,707 width=0) (actual time=0.268..0.268 rows=87,040 loops=1)

  • Index Cond: (pe_id = 4)
24. 0.000 0.006 ↓ 0.0 0 1 / 3

Subquery Scan on *SELECT* 2_1 (cost=1.74..30,389.91 rows=1 width=16) (actual time=0.018..0.018 rows=0 loops=1)

25. 0.000 0.006 ↓ 0.0 0 1 / 3

Nested Loop Anti Join (cost=1.74..30,389.90 rows=1 width=56) (actual time=0.017..0.017 rows=0 loops=1)

  • Join Filter: (av_1_1.pe_id = rtpe_1.id)
26. 0.001 0.006 ↓ 0.0 0 1 / 3

Hash Join (cost=1.31..27,863.32 rows=1,853 width=20) (actual time=0.017..0.017 rows=0 loops=1)

  • Hash Cond: (av_2.pe_id = rtpe_1.baseline_id)
27. 0.002 0.002 ↑ 44,466.0 1 1 / 3

Parallel Seq Scan on rtpezoav av_2 (cost=0.00..27,724.67 rows=44,466 width=20) (actual time=0.005..0.005 rows=1 loops=1)

  • Filter: (ad_id = 1072)
28. 0.000 0.003 ↓ 0.0 0 1 / 3

Hash (cost=1.30..1.30 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
29. 0.002 0.002 ↑ 1.0 1 1 / 3

Seq Scan on rtpe rtpe_1 (cost=0.00..1.30 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (id = 4)
  • Rows Removed by Filter: 23
30. 0.000 0.000 ↓ 0.0 0 / 3

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

  • Index Cond: ((pe_id = 4) AND (zo_id = av_2.zo_id) AND (ad_id = av_2.ad_id) AND (ad_id = 1072))
  • Heap Fetches: 0
Planning time : 2.282 ms
Execution time : 114.907 ms