explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KkVd

Settings
# exclusive inclusive rows x rows loops node
1. 297.244 662.113 ↓ 940.0 940 1

QUERY PLANNested Loop Left Join (cost=3,832.640..3,832.700 rows=1 width=344) (actual time=80.514..662.113 rows=940 loops=1)

  • Join Filter: ((papa_india2delta_hotel.quebec_five = papa_india1delta_hotel.lima_echo_mike) AND (papa_india2delta_hotel.victor_four = papa_india1delta_hotel.bravo_kilo) AND (papa_india2delta_hotel.six_three = papa_india1delta_hotel.uniform) AND (papa_india2delta_hotel.four_charlie_india = papa_india1delta_hotel.six_seven_tango))
  • Rows Removed by Join Filter: 696208
2.          

CTE five_uniform

3. 1.326 5.440 ↓ 849.0 849 1

Nested Loop (cost=1.140..132.610 rows=1 width=44) (actual time=0.032..5.440 rows=849 loops=1)

4. 1.567 1.567 ↓ 849.0 849 1

Index Scan using lima_echo_sierra on papa_victor_lima (cost=0.570..131.770 rows=1 width=24) (actual time=0.023..1.567 rows=849 loops=1)

  • Index Cond: ((lima_mike = 9542) AND (victor_four = ANY ('papa_yankee_six'::integer[])))
  • Filter: ((sierra_bravo > 'november_golf'::double precision) AND (quebec_five = ANY ('victor_sierra'::integer[])))
  • Rows Removed by Filter: 517
5. 2.547 2.547 ↑ 1.0 1 849

Index Only Scan using four_lima on india_kilo (cost=0.570..0.830 rows=1 width=24) (actual time=0.002..0.003 rows=1 loops=849)

  • Index Cond: ((quebec_seven = papa_victor_lima.yankee_xray) AND (four_charlie_india >= 'tango_three'::timestamp without time zone) AND (four_charlie_india <= 'lima_alpha'::timestamp without time zone))
  • Filter: (six_three = ANY ('golf_november'::integer[]))
  • Heap Fetches: 0
6.          

CTE alpha

7. 2.009 63.829 ↓ 821.0 821 1

Nested Loop Left Join (cost=3.560..3,690.770 rows=1 width=98) (actual time=0.907..63.829 rows=821 loops=1)

8. 1.863 61.820 ↓ 821.0 821 1

Nested Loop Left Join (cost=3.140..3,690.310 rows=1 width=110) (actual time=0.897..61.820 rows=821 loops=1)

  • Filter: ((((golf_sierra.quebec_seven five_romeo NULL) AND (tango_mike.four_charlie_india >= 'tango_three'::timestamp without time zone)) OR ((golf_sierra.quebec_seven five_romeo NOT NULL) AND (golf_sierra.four_charlie_india >= 'tango_three'::timestamp without time zone))) AND (((golf_sierra.quebec_seven five_romeo NULL) AND (tango_mike.four_charlie_india < 'lima_alpha'::timestamp without time zone)) OR ((golf_sierra.quebec_seven five_romeo NOT NULL) AND (golf_sierra.four_charlie_india < 'lima_alpha'::timestamp without time zone))) AND ((tango_mike.six_three = ANY ('golf_november'::integer[])) OR ((golf_sierra.four_charlie_india >= 'tango_three'::timestamp without time zone) AND (golf_sierra.four_charlie_india <= 'lima_alpha'::timestamp without time zone) AND (NOT (kilo_uniform.lima_mike five_romeo DISTINCT FROM 9542)) AND (kilo_uniform.four_echo = ANY ('tango_bravo'::integer[])) AND (kilo_uniform.quebec_five = ANY ('victor_sierra'::integer[])) AND (kilo_uniform.victor_four = ANY ('papa_yankee_six'::integer[])) AND (golf_sierra.six_three = ANY ('golf_november'::integer[])))))
9. 1.407 59.957 ↓ 821.0 821 1

Nested Loop Left Join (cost=2.570..3,689.340 rows=1 width=118) (actual time=0.891..59.957 rows=821 loops=1)

10. 1.135 58.550 ↓ 821.0 821 1

Nested Loop Left Join (cost=2.000..3,688.540 rows=1 width=98) (actual time=0.887..58.550 rows=821 loops=1)

11. 1.427 54.952 ↓ 821.0 821 1

Nested Loop (cost=1.570..3,688.090 rows=1 width=82) (actual time=0.879..54.952 rows=821 loops=1)

12. 1.537 51.062 ↓ 821.0 821 1

Nested Loop (cost=1.140..3,687.430 rows=1 width=40) (actual time=0.867..51.062 rows=821 loops=1)

13. 47.062 47.062 ↓ 821.0 821 1

Index Scan using juliet on three_papa charlie_zulu (cost=0.570..3,686.630 rows=1 width=24) (actual time=0.852..47.062 rows=821 loops=1)

  • Index Cond: ((quebec_five = ANY ('victor_sierra'::integer[])) AND (victor_four = ANY ('papa_yankee_six'::integer[])) AND (hotel_quebec five_romeo NOT NULL))
  • Filter: ((NOT (lima_mike five_romeo DISTINCT FROM 9542)) AND (four_echo = 6))
  • Rows Removed by Filter: 62309
14. 2.463 2.463 ↑ 1.0 1 821

Index Only Scan using four_lima on india_kilo tango_mike (cost=0.570..0.790 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=821)

  • Index Cond: ((quebec_seven = charlie_zulu.yankee_xray) AND (four_charlie_india >= 'six_seven_victor'::timestamp without time zone) AND (four_charlie_india <= 'papa_yankee_three'::timestamp without time zone))
  • Heap Fetches: 0
15. 2.463 2.463 ↑ 1.0 1 821

Index Scan using mike on november_alpha zulu_lima (cost=0.430..0.650 rows=1 width=46) (actual time=0.002..0.003 rows=1 loops=821)

  • Index Cond: (quebec_seven = charlie_zulu.hotel_quebec)
16. 2.463 2.463 ↑ 1.0 1 821

Index Scan using bravo_two on four_zulu delta_tango (cost=0.420..0.440 rows=1 width=20) (actual time=0.002..0.003 rows=1 loops=821)

  • Index Cond: (tango_mike.six_three = quebec_seven)
17. 0.000 0.000 ↓ 0.0 0 821

Index Scan using echo_foxtrot on three_papa kilo_uniform (cost=0.570..0.790 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=821)

  • Index Cond: (charlie_zulu.yankee_juliet = quebec_seven)
18. 0.000 0.000 ↓ 0.0 0 821

Index Only Scan using four_lima on india_kilo golf_sierra (cost=0.570..0.590 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=821)

  • Index Cond: (quebec_seven = kilo_uniform.yankee_xray)
  • Heap Fetches: 0
19. 0.000 0.000 ↓ 0.0 0 821

Index Scan using bravo_two on four_zulu three_golf_romeo_echo (cost=0.420..0.440 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=821)

  • Index Cond: (golf_sierra.six_three = quebec_seven)
20.          

CTE hotel_yankee

21. 1.182 66.169 ↓ 730.0 730 1

HashAggregate (cost=0.030..0.040 rows=1 width=28) (actual time=65.801..66.169 rows=730 loops=1)

  • Group Key: kilo_november.lima_echo_mike, kilo_november.bravo_kilo, kilo_november.uniform, kilo_november.six_seven_tango
22. 64.987 64.987 ↓ 821.0 821 1

CTE Scan on alpha kilo_november (cost=0.000..0.020 rows=1 width=28) (actual time=0.910..64.987 rows=821 loops=1)

23.          

CTE november_three

24. 0.001 12.461 ↓ 0.0 0 1

GroupAggregate (cost=8.530..8.560 rows=1 width=28) (actual time=12.461..12.461 rows=0 loops=1)

  • Group Key: india_sierra.quebec_five, india_sierra.victor_four, charlie_bravo1.six_three, charlie_bravo1.four_charlie_india
25. 0.007 12.460 ↓ 0.0 0 1

Sort (cost=8.530..8.540 rows=1 width=228) (actual time=12.460..12.460 rows=0 loops=1)

  • Sort Key: india_sierra.quebec_five, india_sierra.victor_four, charlie_bravo1.six_three, charlie_bravo1.four_charlie_india
  • Sort Method: quicksort Memory: 25kB
26. 0.001 12.453 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.040..8.520 rows=1 width=228) (actual time=12.453..12.453 rows=0 loops=1)

  • Filter: ((three_golf_romeo_xray.golf_romeo five_romeo NULL) OR (three_golf_romeo_xray.golf_romeo <> 5))
27. 0.001 12.452 ↓ 0.0 0 1

Nested Loop (cost=2.470..7.920 rows=1 width=232) (actual time=12.452..12.452 rows=0 loops=1)

28. 0.001 12.451 ↓ 0.0 0 1

Nested Loop (cost=2.340..7.580 rows=2 width=236) (actual time=12.451..12.451 rows=0 loops=1)

29. 0.837 12.450 ↓ 0.0 0 1

Nested Loop (cost=1.130..1.690 rows=4 width=24) (actual time=12.450..12.450 rows=0 loops=1)

30. 1.066 9.915 ↓ 849.0 849 1

Nested Loop (cost=0.570..0.820 rows=1 width=24) (actual time=0.007..9.915 rows=849 loops=1)

31. 6.302 6.302 ↓ 849.0 849 1

CTE Scan on five_uniform india_sierra (cost=0.000..0.020 rows=1 width=16) (actual time=0.001..6.302 rows=849 loops=1)

32. 2.547 2.547 ↑ 1.0 1 849

Index Only Scan using four_lima on india_kilo yankee_november (cost=0.570..0.790 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=849)

  • Index Cond: (quebec_seven = india_sierra.yankee_xray)
  • Heap Fetches: 0
33. 1.698 1.698 ↓ 0.0 0 849

Index Only Scan using papa_victor_three on three_oscar four_charlie_kilo (cost=0.560..0.830 rows=4 width=8) (actual time=0.002..0.002 rows=0 loops=849)

  • Index Cond: (victor_lima = india_sierra.quebec_seven)
  • Heap Fetches: 0
34. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on november_six november_mike (cost=1.200..1.440 rows=3 width=220) (never executed)

  • Recheck Cond: (((quebec_seven = papa_india2november_mike.xray) AND (golf_romeo <> 5)) OR (lima_victor = papa_india2november_mike.xray))
  • Filter: (golf_romeo <> 5)
35. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=1.200..1.200 rows=8 width=0) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on two (cost=0.000..0.570 rows=1 width=0) (never executed)

  • Index Cond: (quebec_seven = papa_india2november_mike.xray)
37. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on india_oscar (cost=0.000..0.630 rows=8 width=0) (never executed)

  • Index Cond: (lima_victor = papa_india2november_mike.xray)
38. 0.000 0.000 ↓ 0.0 0

Index Scan using papa_juliet on whiskey sierra_juliet (cost=0.140..0.160 rows=1 width=16) (never executed)

  • Index Cond: (quebec_seven = november_mike.four_uniform)
  • Filter: ((zulu_mike = ANY ('golf_seven'::text[])) OR (november_mike.lima_victor five_romeo NOT NULL))
39. 0.000 0.000 ↓ 0.0 0

Index Scan using oscar on november_six three_golf_romeo_xray (cost=0.570..0.590 rows=1 width=8) (never executed)

  • Index Cond: (november_mike.lima_victor = quebec_seven)
40.          

CTE golf_xray

41. 1.100 83.814 ↓ 849.0 849 1

Nested Loop (cost=0.330..0.650 rows=1 width=42) (actual time=79.802..83.814 rows=849 loops=1)

42. 0.015 0.015 ↑ 1.0 1 1

Index Scan using delta_zulu on echo_xray (cost=0.290..0.500 rows=1 width=16) (actual time=0.011..0.015 rows=1 loops=1)

  • Index Cond: (quebec_seven = 3738)
43. 1.136 82.699 ↓ 849.0 849 1

Hash Full Join (cost=0.040..0.120 rows=1 width=60) (actual time=79.781..82.699 rows=849 loops=1)

  • Hash Cond: ((five_uniform.quebec_five = hotel_yankee.lima_echo_mike) AND (five_uniform.victor_four = hotel_yankee.bravo_kilo) AND (five_uniform.six_three = hotel_yankee.uniform) AND (five_uniform.four_charlie_india = hotel_yankee.six_seven_tango))
44. 1.110 14.293 ↓ 849.0 849 1

Nested Loop Left Join (cost=0.000..0.060 rows=1 width=32) (actual time=12.499..14.293 rows=849 loops=1)

  • Join Filter: ((five_uniform.quebec_five = november_three.quebec_five) AND (five_uniform.victor_four = november_three.victor_four) AND (five_uniform.six_three = november_three.six_three) AND (five_uniform.four_charlie_india = november_three.four_charlie_india))
45. 0.448 0.448 ↓ 849.0 849 1

CTE Scan on five_uniform (cost=0.000..0.020 rows=1 width=24) (actual time=0.034..0.448 rows=849 loops=1)

46. 12.735 12.735 ↓ 0.0 0 849

CTE Scan on november_three (cost=0.000..0.020 rows=1 width=28) (actual time=0.015..0.015 rows=0 loops=849)

47. 0.421 67.270 ↓ 730.0 730 1

Hash (cost=0.020..0.020 rows=1 width=28) (actual time=67.270..67.270 rows=730 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
48. 66.849 66.849 ↓ 730.0 730 1

CTE Scan on hotel_yankee (cost=0.000..0.020 rows=1 width=28) (actual time=65.804..66.849 rows=730 loops=1)

49. 84.699 84.699 ↓ 849.0 849 1

CTE Scan on golf_xray (cost=0.000..0.020 rows=1 width=42) (actual time=79.805..84.699 rows=849 loops=1)

50. 280.170 280.170 ↓ 821.0 821 849

CTE Scan on alpha (cost=0.000..0.020 rows=1 width=322) (actual time=0.000..0.330 rows=821 loops=849)

Planning time : 141.936 ms
Execution time : 662.979 ms