explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P3p6

Settings
# exclusive inclusive rows x rows loops node
1. 9,347.835 31,127.833 ↓ 65,313.5 130,627 1

Gather (cost=11,771.92..74,195.73 rows=2 width=517) (actual time=27,170.563..31,127.833 rows=130,627 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 983.487 21,779.998 ↓ 21,771.0 43,542 3 / 3

Parallel Append (cost=10,771.92..73,195.53 rows=2 width=517) (actual time=19,086.734..21,779.998 rows=43,542 loops=3)

3. 229.373 2,156.080 ↓ 12,835.0 12,835 1 / 3

Nested Loop Anti Join (cost=11,845.65..18,408.53 rows=1 width=517) (actual time=3,409.020..6,468.240 rows=12,835 loops=1)

4. 127.224 1,866.810 ↓ 2.8 12,835 1 / 3

Hash Left Join (cost=11,845.23..15,148.22 rows=4,548 width=455) (actual time=3,408.989..5,600.431 rows=12,835 loops=1)

  • Hash Cond: (bs.segment_id = bds.segment_id)
5. 122.251 1,477.627 ↓ 2.8 12,835 1 / 3

Hash Join (cost=10,781.75..14,072.79 rows=4,548 width=415) (actual time=2,622.779..4,432.880 rows=12,835 loops=1)

  • Hash Cond: ((bs.booking_id)::text = (b.booking_id)::text)
6. 194.216 1,132.498 ↓ 2.8 12,835 1 / 3

Merge Join (cost=9,960.47..13,188.89 rows=4,572 width=357) (actual time=1,953.527..3,397.494 rows=12,835 loops=1)

  • Merge Cond: (e.ouid = bs.charge_ouid)
7. 95.030 95.030 ↑ 7.0 15,522 1 / 3

Parallel Index Scan using events_ouid_key on events e (cost=0.42..44,408.64 rows=108,437 width=275) (actual time=0.026..285.091 rows=15,522 loops=1)

8. 131.424 843.252 ↑ 5.2 12,836 1 / 3

Materialize (cost=9,959.97..10,291.42 rows=66,289 width=82) (actual time=1,953.471..2,529.756 rows=12,836 loops=1)

9. 445.912 711.828 ↑ 5.2 12,836 1 / 3

Sort (cost=9,959.97..10,125.70 rows=66,289 width=82) (actual time=1,953.447..2,135.483 rows=12,836 loops=1)

  • Sort Key: bs.charge_ouid
  • Worker 0: Sort Method: external merge Disk: 5,144kB
10. 265.915 265.915 ↑ 1.0 66,289 1 / 3

Seq Scan on bookings_segment bs (cost=0.00..1,476.89 rows=66,289 width=82) (actual time=2.903..797.746 rows=66,289 loops=1)

11. 113.667 222.878 ↓ 1.0 22,125 1 / 3

Hash (cost=545.88..545.88 rows=22,032 width=58) (actual time=668.625..668.633 rows=22,125 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,469kB
12. 109.211 109.211 ↓ 1.0 22,125 1 / 3

Seq Scan on bookings b (cost=0.00..545.88 rows=22,032 width=58) (actual time=8.298..327.632 rows=22,125 loops=1)

  • Filter: (booking_date > '2017-01-01'::date)
  • Rows Removed by Filter: 25
13. 132.843 261.959 ↑ 1.0 34,466 1 / 3

Hash (cost=632.66..632.66 rows=34,466 width=40) (actual time=785.870..785.878 rows=34,466 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,936kB
14. 129.116 129.116 ↑ 1.0 34,466 1 / 3

Seq Scan on bookings_detail_segments bds (cost=0.00..632.66 rows=34,466 width=40) (actual time=2.151..387.349 rows=34,466 loops=1)

15. 59.897 59.897 ↓ 0.0 0 12,835 / 3

Index Scan using bookings_detail_pk on bookings_detail bd (cost=0.42..0.71 rows=1 width=62) (actual time=0.014..0.014 rows=0 loops=12,835)

  • Index Cond: (bds.booking_detail_id = booking_detail_id)
16. 6,486.008 18,640.431 ↓ 39,264.0 39,264 3 / 3

Parallel Hash Anti Join (cost=10,771.92..54,786.97 rows=1 width=517) (actual time=17,957.415..18,640.431 rows=39,264 loops=3)

  • Hash Cond: (bds_1.booking_detail_id = bd_1.booking_detail_id)
17. 1,978.642 10,424.613 ↑ 1.2 135,952 2 / 3

Hash Left Join (cost=6,446.79..29,189.22 rows=168,700 width=455) (actual time=5,408.269..15,636.920 rows=135,952 loops=2)

  • Hash Cond: (bs_1.segment_id = bds_1.segment_id)
18. 3,687.829 7,905.753 ↑ 1.2 135,952 2 / 3

Hash Join (cost=5,383.31..27,682.86 rows=168,700 width=415) (actual time=4,597.773..11,858.630 rows=135,952 loops=2)

  • Hash Cond: ((e_1.aggregate_id)::text = (bs_1.aggregate_id)::text)
19. 1,153.705 1,153.705 ↓ 1.2 130,089 2 / 3

Parallel Seq Scan on events e_1 (cost=0.00..10,667.37 rows=108,437 width=275) (actual time=0.025..1,730.557 rows=130,089 loops=2)

20. 1,409.555 3,064.219 ↑ 1.2 53,454 2 / 3

Hash (cost=3,206.11..3,206.11 rows=65,936 width=140) (actual time=4,596.313..4,596.329 rows=53,454 loops=2)

  • Buckets: 32,768 Batches: 4 Memory Usage: 2,634kB
21. 868.107 1,654.665 ↓ 1.0 66,289 2 / 3

Hash Join (cost=821.28..3,206.11 rows=65,936 width=140) (actual time=503.991..2,481.997 rows=66,289 loops=2)

  • Hash Cond: ((bs_1.booking_id)::text = (b_1.booking_id)::text)
22. 452.008 452.008 ↑ 1.0 66,289 2 / 3

Seq Scan on bookings_segment bs_1 (cost=0.00..1,476.89 rows=66,289 width=82) (actual time=1.978..678.012 rows=66,289 loops=2)

23. 173.979 334.550 ↓ 1.0 22,125 2 / 3

Hash (cost=545.88..545.88 rows=22,032 width=58) (actual time=501.817..501.825 rows=22,125 loops=2)

  • Buckets: 32,768 Batches: 1 Memory Usage: 2,469kB
24. 160.571 160.571 ↓ 1.0 22,125 2 / 3

Seq Scan on bookings b_1 (cost=0.00..545.88 rows=22,032 width=58) (actual time=1.642..240.857 rows=22,125 loops=2)

  • Filter: (booking_date > '2017-01-01'::date)
  • Rows Removed by Filter: 25
25. 271.945 540.218 ↑ 1.0 34,466 2 / 3

Hash (cost=632.66..632.66 rows=34,466 width=40) (actual time=810.293..810.327 rows=34,466 loops=2)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,936kB
26. 268.273 268.273 ↑ 1.0 34,466 2 / 3

Seq Scan on bookings_detail_segments bds_1 (cost=0.00..632.66 rows=34,466 width=40) (actual time=1.685..402.409 rows=34,466 loops=2)

27. 1,181.131 1,729.810 ↑ 1.8 45,944 3 / 3

Parallel Hash (cost=2,443.39..2,443.39 rows=80,939 width=62) (actual time=1,729.802..1,729.810 rows=45,944 loops=3)

  • Buckets: 65,536 Batches: 4 Memory Usage: 3,936kB
28. 548.679 548.679 ↑ 1.2 68,916 2 / 3

Parallel Seq Scan on bookings_detail bd_1 (cost=0.00..2,443.39 rows=80,939 width=62) (actual time=0.024..823.018 rows=68,916 loops=2)

Planning time : 14.307 ms
Execution time : 32,607.800 ms