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. 0.000 21,779.998 ↓ 21,771.0 43,542 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. 867.795 6,468.240 ↓ 12,835.0 12,835 1

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. 381.673 5,600.431 ↓ 2.8 12,835 1

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. 366.753 4,432.880 ↓ 2.8 12,835 1

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. 582.647 3,397.494 ↓ 2.8 12,835 1

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. 285.091 285.091 ↑ 7.0 15,522 1

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. 394.273 2,529.756 ↑ 5.2 12,836 1

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. 1,337.737 2,135.483 ↑ 5.2 12,836 1

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: 5144kB
10. 797.746 797.746 ↑ 1.0 66,289 1

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. 341.001 668.633 ↓ 1.0 22,125 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2469kB
12. 327.632 327.632 ↓ 1.0 22,125 1

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. 398.529 785.878 ↑ 1.0 34,466 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2936kB
14. 387.349 387.349 ↑ 1.0 34,466 1

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. 0.014 0.014 ↓ 0.0 0 12,835

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. 1,273.701 18,640.431 ↓ 39,264.0 39,264 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. 2,967.963 15,636.920 ↑ 1.2 135,952 2

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. 5,531.744 11,858.630 ↑ 1.2 135,952 2

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,730.557 1,730.557 ↓ 1.2 130,089 2

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. 2,114.332 4,596.329 ↑ 1.2 53,454 2

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: 32768 Batches: 4 Memory Usage: 2634kB
21. 1,302.160 2,481.997 ↓ 1.0 66,289 2

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. 678.012 678.012 ↑ 1.0 66,289 2

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. 260.968 501.825 ↓ 1.0 22,125 2

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2469kB
24. 240.857 240.857 ↓ 1.0 22,125 2

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. 407.918 810.327 ↑ 1.0 34,466 2

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

  • Buckets: 65536 Batches: 1 Memory Usage: 2936kB
26. 402.409 402.409 ↑ 1.0 34,466 2

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. 906.792 1,729.810 ↑ 1.8 45,944 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: 65536 Batches: 4 Memory Usage: 3936kB
28. 823.018 823.018 ↑ 1.2 68,916 2

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