explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4CV5 : ㅁㅁ

Settings
# exclusive inclusive rows x rows loops node
1. 5.691 82,527.955 ↓ 30.0 30 1

Subquery Scan on romeo_three (cost=4,628.430..4,695.750 rows=1 width=222) (actual time=29,967.236..82,527.955 rows=30 loops=1)

  • Filter: ((romeo_three.november_bravo >= 1) AND (romeo_three.november_bravo <= 30))
  • Rows Removed by Filter: 14626
  • Buffers: shared hit=652279 read=65457
2. 1,124.835 82,522.264 ↓ 14,656.0 14,656 1

WindowAgg (cost=4,628.430..4,695.740 rows=1 width=222) (actual time=29,967.235..82,522.264 rows=14,656 loops=1)

  • Buffers: shared hit=652279 read=65457
3. 53.400 29,957.519 ↓ 14,656.0 14,656 1

Sort (cost=4,628.430..4,628.430 rows=1 width=79) (actual time=29,952.429..29,957.519 rows=14,656 loops=1)

  • Sort Key: quebec.delta_zulu DESC, quebec.delta_yankee DESC
  • Sort Method: quicksort Memory: 2446kB
  • Buffers: shared hit=227911 read=32941
4. 30.216 29,904.119 ↓ 14,656.0 14,656 1

Nested Loop Left Join (cost=3,873.210..4,628.420 rows=1 width=79) (actual time=511.816..29,904.119 rows=14,656 loops=1)

  • Buffers: shared hit=227911 read=32941
5. 20.757 29,683.375 ↓ 14,656.0 14,656 1

Nested Loop Left Join (cost=3,872.650..4,627.780 rows=1 width=74) (actual time=511.790..29,683.375 rows=14,656 loops=1)

  • Buffers: shared hit=154481 read=32941
6. 4.410 8,807.130 ↓ 14,656.0 14,656 1

Nested Loop Anti Join (cost=3,872.090..4,626.990 rows=1 width=69) (actual time=507.029..8,807.130 rows=14,656 loops=1)

  • Join Filter: (tango.three_alpha = six_three.three_alpha)
  • Buffers: shared hit=97027 read=16884
7. 23.185 8,450.976 ↓ 14,656.0 14,656 1

Nested Loop (cost=3,871.250..4,625.720 rows=1 width=69) (actual time=505.935..8,450.976 rows=14,656 loops=1)

  • Buffers: shared hit=53165 read=16728
8. 17.283 542.863 ↓ 1,046.9 14,656 1

Hash Join (cost=3,870.690..4,569.510 rows=14 width=84) (actual time=503.342..542.863 rows=14,656 loops=1)

  • Hash Cond: (((quebec.golf_echo)::text = (six_three.golf_echo)::text) AND (quebec.three_alpha = six_three.three_alpha))
  • Buffers: shared hit=220 read=10579
9. 23.833 23.833 ↑ 1.0 14,744 1

Seq Scan on golf_six quebec (cost=0.000..621.410 rows=14,744 width=69) (actual time=1.571..23.833 rows=14,744 loops=1)

  • Filter: ((six_lima)::text = 'india_tango'::text)
  • Rows Removed by Filter: 6729
  • Buffers: shared hit=218 read=135
10. 3.388 501.747 ↓ 12.2 14,658 1

Hash (cost=3,852.690..3,852.690 rows=1,200 width=19) (actual time=501.747..501.747 rows=14,658 loops=1)

  • Buckets: 16384 (originally 2048) Batches: 1 (originally 1) Memory Usage: 859kB
  • Buffers: shared hit=2 read=10444
11. 498.359 498.359 ↓ 12.2 14,658 1

Index Scan using kilo_charlie on romeo_zulu six_three (cost=0.430..3,852.690 rows=1,200 width=19) (actual time=3.906..498.359 rows=14,658 loops=1)

  • Index Cond: (((five_six)::text = 'kilo_november'::text) AND ((november_whiskey)::text = 'india_five'::text))
  • Filter: ((bravo_tango)::text = 'delta_oscar'::text)
  • Rows Removed by Filter: 187
  • Buffers: shared hit=2 read=10444
12. 7,884.928 7,884.928 ↑ 1.0 1 14,656

Index Only Scan using victor_four on xray_india papa (cost=0.560..4.010 rows=1 width=23) (actual time=0.538..0.538 rows=1 loops=14,656)

  • Index Cond: ((bravo_tango = 'delta_oscar'::text) AND (golf_echo = (quebec.golf_echo)::text) AND (bravo_lima = (quebec.bravo_lima)::text) AND (whiskey_three = 'hotel_november'::text))
  • Heap Fetches: 1
  • Buffers: shared hit=52945 read=6149
13. 14.656 351.744 ↓ 0.0 0 14,656

Nested Loop (cost=0.840..1.260 rows=1 width=19) (actual time=0.024..0.024 rows=0 loops=14,656)

  • Buffers: shared hit=43862 read=156
14. 337.088 337.088 ↓ 0.0 0 14,656

Index Scan using uniform_tango on two_alpha juliet_xray (cost=0.420..0.480 rows=1 width=23) (actual time=0.023..0.023 rows=0 loops=14,656)

  • Index Cond: ((golf_echo)::text = (papa.golf_echo)::text)
  • Filter: ((three_zulu_juliet)::text = 'hotel_november'::text)
  • Buffers: shared hit=43862 read=156
15. 0.000 0.000 ↓ 0.0 0

Index Scan using five_juliet on juliet_golf tango (cost=0.420..0.750 rows=2 width=12) (never executed)

  • Index Cond: ((three_foxtrot)::text = (juliet_xray.three_foxtrot)::text)
16. 20,855.488 20,855.488 ↓ 0.0 0 14,656

Index Scan using juliet_seven on delta_papa kilo_foxtrot (cost=0.560..0.770 rows=2 width=20) (actual time=1.419..1.423 rows=0 loops=14,656)

  • Index Cond: ((golf_echo)::text = (papa.golf_echo)::text)
  • Filter: ((charlie_mike)::text = ANY ('echo'::text[]))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=57454 read=16057
17. 190.528 190.528 ↑ 2.0 1 14,656

Index Scan using juliet_seven on delta_papa yankee_zulu (cost=0.560..0.720 rows=2 width=20) (actual time=0.013..0.013 rows=1 loops=14,656)

  • Index Cond: (((papa.golf_echo)::text = (golf_echo)::text) AND (whiskey_foxtrot = 1))
  • Buffers: shared hit=73430
18.          

SubPlan (for WindowAgg)

19. 43.968 806.080 ↑ 1.0 1 14,656

Aggregate (cost=24.400..24.410 rows=1 width=32) (actual time=0.055..0.055 rows=1 loops=14,656)

  • Buffers: shared hit=54278 read=549
20. 762.112 762.112 ↑ 2.0 5 14,656

Index Scan using mike on hotel_papa juliet_victor (cost=0.420..24.340 rows=10 width=7) (actual time=0.046..0.052 rows=5 loops=14,656)

  • Index Cond: (delta_yankee = quebec.delta_yankee)
  • Buffers: shared hit=54278 read=549
21. 58.624 102.592 ↑ 1.0 1 14,656

Aggregate (cost=24.380..24.390 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=14,656)

  • Buffers: shared hit=54833
22. 43.968 43.968 ↑ 2.0 2 14,656

Index Scan using mike on hotel_papa two_papa (cost=0.420..24.370 rows=4 width=8) (actual time=0.003..0.003 rows=2 loops=14,656)

  • Index Cond: (delta_yankee = quebec.delta_yankee)
  • Filter: ((two_bravo)::text = 'delta_oscar'::text)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=54826
23. 25.348 50,531.238 ↑ 1.0 1 12,674

Limit (cost=1.990..18.160 rows=1 width=32) (actual time=3.986..3.987 rows=1 loops=12,674)

  • Buffers: shared hit=196343 read=31967
24. 25.348 50,505.890 ↑ 1.0 1 12,674

Nested Loop (cost=1.990..18.160 rows=1 width=32) (actual time=3.985..3.985 rows=1 loops=12,674)

  • Buffers: shared hit=196343 read=31967
25. 38.022 44,016.802 ↑ 1.0 1 12,674

Nested Loop (cost=1.550..17.650 rows=1 width=7) (actual time=3.473..3.473 rows=1 loops=12,674)

  • Buffers: shared hit=150694 read=26901
26. 25.348 34,181.778 ↑ 1.0 1 12,674

Nested Loop (cost=0.990..17.030 rows=1 width=20) (actual time=2.697..2.697 rows=1 loops=12,674)

  • Buffers: shared hit=93732 read=20474
27. 13,066.894 13,066.894 ↑ 1.0 1 12,674

Index Scan using three_zulu_xray on seven_mike india_november (cost=0.430..8.450 rows=1 width=4) (actual time=1.030..1.031 rows=1 loops=12,674)

  • Index Cond: (((golf_echo)::text = (papa.golf_echo)::text) AND (three_alpha = six_three.three_alpha))
  • Buffers: shared hit=43020 read=7787
28. 21,089.536 21,089.536 ↑ 1.0 1 12,674

Index Scan using yankee_yankee on five_uniform uniform_two (cost=0.560..8.580 rows=1 width=24) (actual time=1.664..1.664 rows=1 loops=12,674)

  • Index Cond: (charlie_oscar = india_november.charlie_oscar)
  • Buffers: shared hit=50712 read=12687
29. 9,797.002 9,797.002 ↑ 1.0 1 12,674

Index Scan using xray_delta on xray_victor seven_foxtrot (cost=0.560..0.620 rows=1 width=27) (actual time=0.773..0.773 rows=1 loops=12,674)

  • Index Cond: ((victor_alpha)::text = (uniform_two.victor_alpha)::text)
  • Buffers: shared hit=56962 read=6427
30. 6,463.740 6,463.740 ↑ 1.0 1 12,674

Index Scan using whiskey_delta on five_oscar four (cost=0.430..0.510 rows=1 width=51) (actual time=0.510..0.510 rows=1 loops=12,674)

  • Index Cond: ((six_romeo)::text = (seven_foxtrot.six_romeo)::text)
  • Buffers: shared hit=45649 read=5066