explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2VKd

Settings
# exclusive inclusive rows x rows loops node
1. 5.916 373,613.961 ↓ 2.0 24 1

GroupAggregate (cost=107,811.460..107,812.840 rows=12 width=142) (actual time=373,605.377..373,613.961 rows=24 loops=1)

  • Group Key: xray_six.two_charlie_oscar, xray_six.five_zulu, xray_six.hotel_sierra_seven, xray_six.three_alpha, xray_six.foxtrot, xray_six.lima_romeo
2. 24.180 373,608.045 ↓ 328.5 7,884 1

Sort (cost=107,811.460..107,811.520 rows=24 width=126) (actual time=373,605.091..373,608.045 rows=7,884 loops=1)

  • Sort Key: xray_six.two_charlie_oscar, xray_six.five_zulu DESC, xray_six.three_alpha, xray_six.foxtrot, xray_six.lima_romeo
  • Sort Method: quicksort Memory: 1301kB
3. 10.840 373,583.865 ↓ 328.5 7,884 1

Nested Loop (cost=4,368.570..107,810.910 rows=24 width=126) (actual time=113.375..373,583.865 rows=7,884 loops=1)

4. 0.041 0.041 ↑ 1.0 24 1

Seq Scan on oscar_delta_echo xray_six (cost=0.000..1.300 rows=24 width=46) (actual time=0.008..0.041 rows=24 loops=1)

  • Filter: (hotel_sierra_seven = 3156)
5. 2,141.832 373,572.984 ↓ 328.0 328 24

Nested Loop Left Join (cost=4,368.570..4,492.050 rows=1 width=100) (actual time=103.510..15,565.541 rows=328 loops=24)

  • Join Filter: (two_five.oscar_charlie = six_alpha.quebec_seven)
  • Rows Removed by Join Filter: 215496
6. 1,709.832 369,144.792 ↓ 328.0 328 24

Nested Loop Left Join (cost=4,368.570..4,471.590 rows=1 width=89) (actual time=103.318..15,381.033 rows=328 loops=24)

  • Join Filter: ((alpha_quebec.hotel_sierra_seven = hotel_tango.hotel_sierra_seven) AND (xray_quebec.whiskey = hotel_tango.whiskey))
  • Rows Removed by Join Filter: 174387
7. 38.652 352,676.112 ↓ 328.0 328 24

Nested Loop (cost=302.470..405.420 rows=1 width=29) (actual time=34.420..14,694.838 rows=328 loops=24)

8. 23.988 135.936 ↓ 328.0 328 24

Nested Loop (cost=254.260..353.170 rows=1 width=24) (actual time=1.162..5.664 rows=328 loops=24)

9. 29.760 56.760 ↓ 65.6 328 24

Bitmap Heap Scan on delta_whiskey six_alpha (cost=253.970..311.610 rows=5 width=20) (actual time=1.150..2.365 rows=328 loops=24)

  • Recheck Cond: ((three_alpha = xray_six.three_alpha) AND (hotel_sierra_seven = xray_six.hotel_sierra_seven))
  • Filter: (lima_sierra(mike_romeo, ('victor_foxtrot'::date)::timestamp with time zone) > xray_six.lima_romeo)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=4824
10. 0.360 27.000 ↓ 0.0 0 24

BitmapAnd (cost=253.970..253.970 rows=15 width=0) (actual time=1.125..1.125 rows=0 loops=24)

11. 0.816 0.816 ↓ 1.2 329 24

Bitmap Index Scan on bravo_papa (cost=0.000..6.420 rows=267 width=0) (actual time=0.034..0.034 rows=329 loops=24)

  • Index Cond: (three_alpha = xray_six.three_alpha)
12. 25.824 25.824 ↓ 1.3 16,743 24

Bitmap Index Scan on oscar_echo (cost=0.000..247.290 rows=13,183 width=0) (actual time=1.076..1.076 rows=16,743 loops=24)

  • Index Cond: (hotel_sierra_seven = xray_six.hotel_sierra_seven)
13. 55.188 55.188 ↑ 1.0 1 7,884

Index Scan using oscar_delta_six on seven_romeo four_five (cost=0.290..8.310 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=7,884)

  • Index Cond: (quebec_seven = six_alpha.five_charlie)
  • Filter: (hotel_sierra_seven = xray_six.hotel_sierra_seven)
14. 4,210.056 352,501.524 ↑ 1.0 1 7,884

Bitmap Heap Scan on three_mike hotel_tango (cost=48.210..52.240 rows=1 width=21) (actual time=44.472..44.711 rows=1 loops=7,884)

  • Recheck Cond: ((oscar_charlie = six_alpha.quebec_seven) AND (hotel_sierra_seven = xray_six.hotel_sierra_seven) AND (three_alpha = xray_six.three_alpha))
  • Filter: ((xray_six.lima_romeo >= uniform_four) AND (xray_six.lima_romeo <= mike_golf) AND (two_lima = 0) AND (lima_sierra(mike_romeo, ('victor_foxtrot'::date)::timestamp with time zone) > xray_six.lima_romeo))
  • Rows Removed by Filter: 335
  • Heap Blocks: exact=2222100
15. 1,773.900 348,291.468 ↓ 0.0 0 7,884

BitmapAnd (cost=48.210..48.210 rows=1 width=0) (actual time=44.177..44.177 rows=0 loops=7,884)

16. 283.824 283.824 ↓ 1.2 336 7,884

Bitmap Index Scan on two_india (cost=0.000..6.620 rows=291 width=0) (actual time=0.036..0.036 rows=336 loops=7,884)

  • Index Cond: (oscar_charlie = six_alpha.quebec_seven)
17. 346,233.744 346,233.744 ↓ 91.9 117,316 7,884

Bitmap Index Scan on yankee_xray (cost=0.000..41.330 rows=1,277 width=0) (actual time=43.916..43.916 rows=117,316 loops=7,884)

  • Index Cond: ((hotel_sierra_seven = xray_six.hotel_sierra_seven) AND (three_alpha = xray_six.three_alpha))
18. 9,870.768 14,758.848 ↓ 532.0 532 7,884

GroupAggregate (cost=4,066.100..4,066.150 rows=1 width=130) (actual time=0.217..1.872 rows=532 loops=7,884)

  • Group Key: alpha_quebec.hotel_sierra_seven, alpha_quebec.three_alpha, xray_quebec.whiskey, (xray_six.five_zulu), (xray_six.foxtrot), (xray_six.lima_romeo), november_uniform.six_mike, echo.six_mike
19. 3,283.032 4,888.080 ↓ 1,036.0 1,036 7,884

Sort (cost=4,066.100..4,066.110 rows=1 width=66) (actual time=0.208..0.620 rows=1,036 loops=7,884)

  • Sort Key: alpha_quebec.three_alpha, xray_quebec.whiskey, november_uniform.six_mike, echo.six_mike
  • Sort Method: quicksort Memory: 272kB
20. 21.552 1,605.048 ↓ 1,036.0 1,036 24

Result (cost=1.850..4,066.090 rows=1 width=66) (actual time=0.146..66.877 rows=1,036 loops=24)

  • One-Time Filter: (xray_six.hotel_sierra_seven = 3156)
21. 30.680 1,583.496 ↓ 1,036.0 1,036 24

Nested Loop Left Join (cost=1.850..4,066.090 rows=1 width=66) (actual time=0.144..65.979 rows=1,036 loops=24)

22. 63.164 1,453.344 ↓ 1,036.0 1,036 24

Nested Loop Left Join (cost=1.420..4,057.630 rows=1 width=29) (actual time=0.125..60.556 rows=1,036 loops=24)

  • Join Filter: ((echo.hotel_sierra_seven = victor_whiskey.hotel_sierra_seven) AND (echo.three_alpha = victor_whiskey.three_alpha))
  • Rows Removed by Join Filter: 1036
23. 58.368 1,365.312 ↓ 1,036.0 1,036 24

Nested Loop (cost=1.420..4,056.610 rows=1 width=24) (actual time=0.116..56.888 rows=1,036 loops=24)

24. 36.456 923.544 ↓ 1,065.0 1,065 24

Nested Loop (cost=0.860..4,048.660 rows=1 width=44) (actual time=0.078..38.481 rows=1,065 loops=24)

  • Join Filter: (victor_whiskey.three_alpha = alpha_quebec.three_alpha)
25. 22.824 269.328 ↓ 7.9 1,170 24

Nested Loop (cost=0.430..2,358.020 rows=148 width=20) (actual time=0.026..11.222 rows=1,170 loops=24)

26. 0.120 0.120 ↑ 1.0 2 24

Seq Scan on victor_whiskey (cost=0.000..1.020 rows=2 width=8) (actual time=0.003..0.005 rows=2 loops=24)

27. 246.384 246.384 ↓ 7.9 585 48

Index Scan using bravo_zulu on quebec_foxtrot india (cost=0.430..1,177.760 rows=74 width=12) (actual time=0.020..5.133 rows=585 loops=48)

  • Index Cond: ((hotel_sierra_seven = 3156) AND (three_alpha = victor_whiskey.three_alpha))
  • Filter: (three_november <> november_november ('kilo_delta'::integer[]))
  • Rows Removed by Filter: 4322
28. 617.760 617.760 ↑ 1.0 1 28,080

Index Scan using sierra_hotel on yankee_victor alpha_quebec (cost=0.430..11.410 rows=1 width=40) (actual time=0.019..0.022 rows=1 loops=28,080)

  • Index Cond: (november_golf = india.quebec_seven)
  • Filter: ((NOT two_golf) AND (hotel_sierra_seven = 3156) AND (sierra_xray = 307) AND (kilo_india = 2) AND (india.three_alpha = three_alpha) AND ((four_mike = papa('two_charlie_foxtrot'::text, (xray_six.lima_romeo)::timestamp with time zone)) OR ((delta_bravo <= xray_six.lima_romeo) AND (lima_sierra(mike_hotel, 'victor_foxtrot'::date) >= xray_six.lima_romeo) AND (lima_sierra(mike_romeo, ('victor_foxtrot'::date)::timestamp with time zone) > xray_six.lima_romeo))))
  • Rows Removed by Filter: 18
29. 383.400 383.400 ↑ 1.0 1 25,560

Index Scan using seven_india on november_papa xray_quebec (cost=0.560..7.940 rows=1 width=16) (actual time=0.013..0.015 rows=1 loops=25,560)

  • Index Cond: ((hotel_sierra_seven = 3156) AND (sierra_victor = alpha_quebec.sierra_victor))
  • Filter: ((xray_six.lima_romeo >= uniform_four) AND (xray_six.lima_romeo <= mike_golf) AND (three_november = ANY ('mike_mike'::integer[])) AND (uniform_xray <> 5) AND (two_lima = 0) AND ((alpha_quebec.four_mike = papa('two_charlie_foxtrot'::text, (xray_six.lima_romeo)::timestamp with time zone)) OR ((alpha_quebec.delta_bravo <= xray_six.lima_romeo) AND (lima_sierra(alpha_quebec.mike_hotel, 'victor_foxtrot'::date) >= xray_six.lima_romeo) AND (lima_sierra(alpha_quebec.mike_romeo, ('victor_foxtrot'::date)::timestamp with time zone) > xray_six.lima_romeo) AND (lima_sierra(kilo_november, 'victor_foxtrot'::date) > xray_six.lima_romeo))))
  • Rows Removed by Filter: 12
30. 24.868 24.868 ↑ 1.0 1 24,868

Seq Scan on four_six echo (cost=0.000..1.010 rows=1 width=13) (actual time=0.001..0.001 rows=1 loops=24,868)

  • Filter: ((uniform_yankee)::text = 'lima_xray'::text)
31. 99.472 99.472 ↑ 1.0 1 24,868

Index Scan using hotel_sierra_hotel on alpha_mike november_uniform (cost=0.420..8.440 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=24,868)

  • Index Cond: ((hotel_sierra_seven = victor_whiskey.hotel_sierra_seven) AND (three_alpha = victor_whiskey.three_alpha) AND ((uniform_yankee)::text = 'hotel_xray'::text))
32. 2,286.360 2,286.360 ↑ 1.0 657 7,884

Seq Scan on two_five (cost=0.000..12.210 rows=657 width=12) (actual time=0.006..0.290 rows=657 loops=7,884)

  • Filter: (hotel_sierra_seven = 3156)
Planning time : 11.733 ms