explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wrwf

Settings
# exclusive inclusive rows x rows loops node
1. 4.937 368,735.826 ↓ 8,239.5 16,479 1

Group (cost=118,807.320..118,807.350 rows=2 width=572) (actual time=368,729.088..368,735.826 rows=16,479 loops=1)

  • Group Key: romeo.november_lima, india_sierra.yankee_juliet, bravo_papa.november_lima, bravo_papa.india_whiskey, xray_romeo.six_mike
2. 89.196 368,730.889 ↓ 8,239.5 16,479 1

Sort (cost=118,807.320..118,807.320 rows=2 width=572) (actual time=368,729.086..368,730.889 rows=16,479 loops=1)

  • Sort Key: romeo.november_lima, india_sierra.yankee_juliet, bravo_papa.november_lima, bravo_papa.india_whiskey, xray_romeo.six_mike
  • Sort Method: quicksort Memory: 4745kB
3. 13.483 368,641.693 ↓ 8,239.5 16,479 1

Nested Loop (cost=118,791.100..118,807.310 rows=2 width=572) (actual time=368,579.732..368,641.693 rows=16,479 loops=1)

4. 7.135 368,611.731 ↓ 8,239.5 16,479 1

Nested Loop (cost=118,790.960..118,800.970 rows=2 width=516) (actual time=368,579.717..368,611.731 rows=16,479 loops=1)

5. 4.521 368,555.159 ↓ 8,239.5 16,479 1

Hash Join (cost=118,790.820..118,794.640 rows=2 width=460) (actual time=368,550.660..368,555.159 rows=16,479 loops=1)

  • Hash Cond: ((india_sierra.yankee_juliet)::text = (bravo_papa.sierra_delta)::text)
6. 0.032 0.032 ↑ 1.0 58 1

Seq Scan on zulu_papa india_sierra (cost=0.000..3.580 rows=58 width=60) (actual time=0.015..0.032 rows=58 loops=1)

7. 5.018 368,550.606 ↓ 8,239.5 16,479 1

Hash (cost=118,790.800..118,790.800 rows=2 width=482) (actual time=368,550.606..368,550.606 rows=16,479 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2906kB
8. 2.376 368,545.588 ↓ 8,239.5 16,479 1

Subquery Scan on bravo_papa (cost=118,790.750..118,790.800 rows=2 width=482) (actual time=368,534.182..368,545.588 rows=16,479 loops=1)

9. 6.607 368,543.212 ↓ 8,239.5 16,479 1

Unique (cost=118,790.750..118,790.780 rows=2 width=482) (actual time=368,534.180..368,543.212 rows=16,479 loops=1)

10. 94.835 368,536.605 ↓ 13,097.5 26,195 1

Sort (cost=118,790.750..118,790.750 rows=2 width=482) (actual time=368,534.178..368,536.605 rows=26,195 loops=1)

  • Sort Key: alpha_two.six_mike, alpha_two.echo_five, kilo_foxtrot.november_lima, alpha_two.yankee_three, alpha_two.seven_quebec
  • Sort Method: quicksort Memory: 7727kB
11. 1.775 368,441.770 ↓ 13,097.5 26,195 1

Append (cost=18,061.200..118,790.740 rows=2 width=482) (actual time=14,030.364..368,441.770 rows=26,195 loops=1)

12. 17.371 14,058.432 ↓ 16,452.0 16,452 1

Unique (cost=18,061.200..18,061.210 rows=1 width=141) (actual time=14,030.363..14,058.432 rows=16,452 loops=1)

13. 236.140 14,041.061 ↓ 75,079.0 75,079 1

Sort (cost=18,061.200..18,061.200 rows=1 width=141) (actual time=14,030.362..14,041.061 rows=75,079 loops=1)

  • Sort Key: alpha_two.six_mike, alpha_two.echo_five, kilo_foxtrot.november_lima, alpha_two.yankee_three
  • Sort Method: quicksort Memory: 23015kB
14. 130.172 13,804.921 ↓ 75,079.0 75,079 1

Nested Loop (cost=1.810..18,061.190 rows=1 width=141) (actual time=5.035..13,804.921 rows=75,079 loops=1)

15. 176.234 10,723.301 ↓ 368,931.0 368,931 1

Nested Loop (cost=1.400..18,060.730 rows=1 width=63) (actual time=1.826..10,723.301 rows=368,931 loops=1)

16. 96.618 8,702.412 ↓ 52,704.4 368,931 1

Nested Loop (cost=0.980..18,057.610 rows=7 width=66) (actual time=1.796..8,702.412 rows=368,931 loops=1)

17. 353.325 353.325 ↓ 28,359.0 28,359 1

Index Scan using four_golf on zulu_oscar papa (cost=0.420..17,837.110 rows=1 width=33) (actual time=0.890..353.325 rows=28,359 loops=1)

  • Index Cond: ((sierra_kilo_lima >= 'charlie'::timestamp without time zone) AND (sierra_kilo_lima <= 'echo_india'::timestamp without time zone))
18. 8,252.469 8,252.469 ↑ 8.8 13 28,359

Index Scan using kilo_bravo on quebec foxtrot (cost=0.560..219.360 rows=114 width=99) (actual time=0.240..0.291 rows=13 loops=28,359)

  • Index Cond: ((oscar_six)::text = (papa.oscar_six)::text)
  • Filter: (november_juliet_alpha > 'november_golf'::numeric)
  • Rows Removed by Filter: 2
19. 1,844.655 1,844.655 ↑ 1.0 1 368,931

Index Scan using hotel_golf on bravo_six kilo_foxtrot (cost=0.410..0.440 rows=1 width=63) (actual time=0.005..0.005 rows=1 loops=368,931)

  • Index Cond: ((sierra_quebec)::text = (foxtrot.sierra_quebec)::text)
  • Filter: (((six_mike)::text ~~ 'kilo_romeo'::text) AND (tango = 'uniform'::bpchar))
20. 2,951.448 2,951.448 ↓ 0.0 0 368,931

Index Scan using seven_romeo on xray_zulu alpha_two (cost=0.420..0.450 rows=1 width=144) (actual time=0.007..0.008 rows=0 loops=368,931)

  • Index Cond: ((delta_quebec)::text = (foxtrot.delta_quebec)::text)
  • Filter: (((six_mike)::text ~~ 'five'::text) AND ((three_oscar)::text = 'yankee_alpha'::text) AND (sierra_four = 'four_uniform'::bpchar) AND ((seven_quebec)::text = 'india_juliet (...)
  • Rows Removed by Filter: 1
21. 5.573 354,381.563 ↓ 9,743.0 9,743 1

Unique (cost=100,729.490..100,729.500 rows=1 width=141) (actual time=354,370.278..354,381.563 rows=9,743 loops=1)

22. 103.057 354,375.990 ↓ 23,346.0 23,346 1

Sort (cost=100,729.490..100,729.490 rows=1 width=141) (actual time=354,370.276..354,375.990 rows=23,346 loops=1)

  • Sort Key: xray_whiskey1.six_mike, xray_whiskey1.echo_five, oscar_mike1.november_lima, xray_whiskey1.yankee_three
  • Sort Method: quicksort Memory: 6970kB
23. 101.157 354,272.933 ↓ 23,346.0 23,346 1

Nested Loop (cost=1,478.470..100,729.480 rows=1 width=141) (actual time=53,497.871..354,272.933 rows=23,346 loops=1)

24. 0.000 349,230.392 ↓ 39,217.3 117,652 1

Nested Loop (cost=1,478.050..100,723.850 rows=3 width=63) (actual time=53,352.629..349,230.392 rows=117,652 loops=1)

25. 2,852.342 121,942.170 ↓ 16,995.6 10,333,318 1

Nested Loop (cost=1,477.500..100,337.220 rows=608 width=63) (actual time=157.506..121,942.170 rows=10,333,318 loops=1)

26. 139.026 139.026 ↓ 34.0 34 1

Seq Scan on bravo_six juliet (cost=0.000..2,000.910 rows=1 width=63) (actual time=0.538..139.026 rows=34 loops=1)

  • Filter: (((six_mike)::text ~~ 'kilo_romeo'::text) AND (tango = 'uniform'::bpchar))
  • Rows Removed by Filter: 22513
27. 88,224.458 118,950.802 ↓ 4.2 303,921 34

Bitmap Heap Scan on sierra_kilo_four kilo_papa (cost=1,477.500..97,609.290 rows=72,702 width=66) (actual time=976.555..3,498.553 rows=303,921 loops=34)

  • Recheck Cond: ((sierra_quebec)::text = (oscar_mike1.sierra_quebec)::text)
  • Rows Removed by Index Recheck: 739944
  • Heap Blocks: exact=5922454 lossy=1851479
28. 30,726.344 30,726.344 ↓ 4.5 323,864 34

Bitmap Index Scan on alpha_quebec_four (cost=0.000..1,459.320 rows=72,702 width=0) (actual time=903.716..903.716 rows=323,864 loops=34)

  • Index Cond: ((sierra_quebec)::text = (oscar_mike1.sierra_quebec)::text)
29. 227,332.996 227,332.996 ↓ 0.0 0 10,333,318

Index Scan using november_delta on xray_november alpha_quebec_whiskey (cost=0.550..0.630 rows=1 width=66) (actual time=0.022..0.022 rows=0 loops=10,333,318)

  • Index Cond: ((november_juliet_hotel)::text = (kilo_papa.november_juliet_hotel)::text)
  • Filter: ((mike = 'uniform'::bpchar) AND (three_six = 'uniform'::bpchar) AND (tango = 'uniform'::bpchar) AND ((delta_kilo)::date >= 'golf_lima'::date) AND ((delta_kilo)::hotel_quebec_three (...)
  • Rows Removed by Filter: 1
30. 4,941.384 4,941.384 ↓ 0.0 0 117,652

Index Scan using seven_romeo on xray_zulu victor (cost=0.420..1.870 rows=1 width=144) (actual time=0.036..0.042 rows=0 loops=117,652)

  • Index Cond: ((delta_quebec)::text = (alpha_quebec_whiskey.four_two)::text)
  • Filter: (((six_mike)::text ~~ 'five'::text) AND ((three_oscar)::text = 'yankee_alpha'::text) AND (sierra_four = 'four_uniform'::bpchar) AND ((seven_quebec)::text = 'india_juliet (...)
  • Rows Removed by Filter: 1
31. 49.437 49.437 ↑ 1.0 1 16,479

Index Scan using hotel_quebec_india on six_foxtrot xray_romeo (cost=0.140..3.160 rows=1 width=220) (actual time=0.003..0.003 rows=1 loops=16,479)

  • Index Cond: ((four_juliet)::text = (bravo_papa.two)::text)
32. 16.479 16.479 ↑ 1.0 1 16,479

Index Scan using golf_juliet on india_romeo romeo (cost=0.140..3.160 rows=1 width=220) (actual time=0.001..0.001 rows=1 loops=16,479)

  • Index Cond: ((golf_juliet)::text = (bravo_papa.golf_alpha)::text)
Planning time : 3.218 ms
Execution time : 368,758.003 ms