explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LFYT

Settings
# exclusive inclusive rows x rows loops node
1. 1,413.148 284,100.648 ↓ 366,642.0 366,642 1

HashAggregate (cost=2,667,564.660..2,667,564.680 rows=1 width=124) (actual time=283,920.200..284,100.648 rows=366,642 loops=1)

  • Group Key: sierra_delta.yankee_victor, foxtrot_papa.quebec_alpha, foxtrot_papa.mike_delta, ((('sierra_tango'::text || foxtrot_papa.zulu_india) || 'two_uniform'::text) || foxtrot_papa.oscar_whiskey), sierra_delta.xray_delta, sierra_delta.zulu_kilo, sierra_delta.four, sierra_delta.hotel_victor, xray_sierra.romeo_five, three_kilo.papa_yankee
2. 786.393 282,687.500 ↓ 372,459.0 372,459 1

Nested Loop Left Join (cost=90.790..2,667,564.640 rows=1 width=124) (actual time=2.071..282,687.500 rows=372,459 loops=1)

3. 151,635.971 271,581.175 ↓ 368,569.0 368,569 1

Nested Loop Left Join (cost=90.360..2,667,562.150 rows=1 width=117) (actual time=2.063..271,581.175 rows=368,569 loops=1)

  • Join Filter: ((three_kilo.victor_charlie)::text = hotel_three.romeo_five)
  • Rows Removed by Join Filter: 533,994,142
4. 413.560 58,025.612 ↓ 368,569.0 368,569 1

Nested Loop Left Join (cost=90.360..2,667,492.300 rows=1 width=125) (actual time=1.144..58,025.612 rows=368,569 loops=1)

5. 3,331.758 56,878.768 ↓ 366,642.0 366,642 1

Nested Loop Left Join (cost=89.940..2,667,491.040 rows=1 width=110) (actual time=1.141..56,878.768 rows=366,642 loops=1)

6. 388.465 52,813.726 ↓ 366,642.0 366,642 1

Nested Loop (cost=74.680..2,667,474.140 rows=1 width=102) (actual time=1.119..52,813.726 rows=366,642 loops=1)

7. 211.258 48,758.841 ↓ 366,642.0 366,642 1

Nested Loop (cost=74.260..2,667,471.790 rows=1 width=58) (actual time=1.103..48,758.841 rows=366,642 loops=1)

8. 242.751 47,814.299 ↓ 366,642.0 366,642 1

Nested Loop (cost=74.120..2,667,471.620 rows=1 width=62) (actual time=1.097..47,814.299 rows=366,642 loops=1)

9. 40,653.626 45,371.696 ↓ 366,642.0 366,642 1

Nested Loop (cost=73.690..2,667,469.830 rows=1 width=62) (actual time=1.081..45,371.696 rows=366,642 loops=1)

10. 638.860 3,251.454 ↓ 1.2 366,654 1

Hash Join (cost=66.260..192,836.020 rows=295,765 width=58) (actual time=1.038..3,251.454 rows=366,654 loops=1)

  • Hash Cond: (sierra_delta.india = papa_quebec.three_five)
11. 2,611.596 2,611.596 ↓ 1.0 732,150 1

Seq Scan on five_four sierra_delta (cost=0.000..187,113.150 rows=719,721 width=62) (actual time=0.012..2,611.596 rows=732,150 loops=1)

  • Filter: ((xray_delta = ANY ('mike_november'::text[])) AND (quebec_sierra <> 'five_five'::text))
  • Rows Removed by Filter: 1,789,295
12. 0.150 0.998 ↑ 1.0 505 1

Hash (cost=59.950..59.950 rows=505 width=4) (actual time=0.998..0.998 rows=505 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
13. 0.848 0.848 ↑ 1.0 505 1

Seq Scan on yankee_foxtrot papa_quebec (cost=0.000..59.950 rows=505 width=4) (actual time=0.011..0.848 rows=505 loops=1)

  • Filter: (papa_three five_romeo NOT FALSE)
  • Rows Removed by Filter: 690
14. 0.000 1,466.616 ↑ 1.0 1 366,654

Index Only Scan using sierra_papa on uniform_oscar quebec_three (cost=7.430..8.360 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=366,654)

  • Index Cond: ((yankee_victor = sierra_delta.yankee_victor) AND (hotel_quebec = (delta_four 2)))
  • Heap Fetches: 14,221
15.          

SubPlan (for Index Only Scan)

16. 733.308 39,965.286 ↑ 1.0 1 366,654

Result (cost=6.980..6.990 rows=1 width=0) (actual time=0.108..0.109 rows=1 loops=366,654)

17.          

Initplan (for Result)

18. 366.654 39,231.978 ↑ 1.0 1 366,654

Limit (cost=0.430..6.980 rows=1 width=4) (actual time=0.107..0.107 rows=1 loops=366,654)

19. 38,865.324 38,865.324 ↑ 3.0 1 366,654

Index Scan Backward using sierra_papa on uniform_oscar (cost=0.430..20.080 rows=3 width=4) (actual time=0.106..0.106 rows=1 loops=366,654)

  • Index Cond: ((yankee_victor = sierra_delta.yankee_victor) AND (hotel_quebec five_romeo NOT NULL))
  • Filter: ((papa_two <> 'seven_four'::text) AND (papa_two <> 'oscar_uniform'::text))
  • Rows Removed by Filter: 0
20. 733.308 39,965.286 ↑ 1.0 1 366,654

Result (cost=6.980..6.990 rows=1 width=0) (actual time=0.108..0.109 rows=1 loops=366,654)

21.          

Initplan (for Result)

22. 366.654 39,231.978 ↑ 1.0 1 366,654

Limit (cost=0.430..6.980 rows=1 width=4) (actual time=0.107..0.107 rows=1 loops=366,654)

23. 38,865.324 38,865.324 ↑ 3.0 1 366,654

Index Scan Backward using sierra_papa on uniform_oscar (cost=0.430..20.080 rows=3 width=4) (actual time=0.106..0.106 rows=1 loops=366,654)

  • Index Cond: ((yankee_victor = sierra_delta.yankee_victor) AND (hotel_quebec five_romeo NOT NULL))
  • Filter: ((papa_two <> 'seven_four'::text) AND (papa_two <> 'oscar_uniform'::text))
  • Rows Removed by Filter: 0
24. 2,199.852 2,199.852 ↑ 15.0 1 366,642

Index Only Scan using zulu_romeo on quebec_charlie bravo_foxtrot (cost=0.430..1.640 rows=15 width=8) (actual time=0.005..0.006 rows=1 loops=366,642)

  • Index Cond: ((yankee_victor = sierra_delta.yankee_victor) AND (foxtrot_two = 'delta_sierra'::text))
  • Heap Fetches: 3,717
25. 733.284 733.284 ↑ 1.0 1 366,642

Index Only Scan using november_delta on lima_papa echo (cost=0.140..0.160 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=366,642)

  • Index Cond: (romeo_juliet = bravo_foxtrot.romeo_juliet)
  • Heap Fetches: 0
26. 3,666.420 3,666.420 ↑ 1.0 1 366,642

Index Scan using six on yankee_alpha foxtrot_papa (cost=0.420..2.340 rows=1 width=60) (actual time=0.010..0.010 rows=1 loops=366,642)

  • Index Cond: (two_quebec = sierra_delta.two_quebec)
27. 0.000 733.284 ↓ 0.0 0 366,642

Index Scan using oscar_delta on november_quebec hotel_hotel (cost=15.260..16.890 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=366,642)

  • Index Cond: (zulu_foxtrot = (delta_four 3))
  • Filter: (sierra_delta.yankee_victor = yankee_victor)
28.          

SubPlan (for Index Scan)

29. 366.642 2,566.494 ↑ 1.0 1 366,642

Aggregate (cost=14.830..14.840 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=366,642)

30. 2,199.852 2,199.852 ↓ 0.0 0 366,642

Index Scan using lima_kilo on november_quebec (cost=0.420..14.820 rows=3 width=8) (actual time=0.005..0.006 rows=0 loops=366,642)

  • Index Cond: (yankee_victor = sierra_delta.yankee_victor)
31. 366.642 2,566.494 ↑ 1.0 1 366,642

Aggregate (cost=14.830..14.840 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=366,642)

32. 2,199.852 2,199.852 ↓ 0.0 0 366,642

Index Scan using lima_kilo on november_quebec (cost=0.420..14.820 rows=3 width=8) (actual time=0.005..0.006 rows=0 loops=366,642)

  • Index Cond: (yankee_victor = sierra_delta.yankee_victor)
33. 733.284 733.284 ↓ 0.0 0 366,642

Index Only Scan using bravo_hotel on lima_victor hotel_three (cost=0.420..1.250 rows=1 width=23) (actual time=0.002..0.002 rows=0 loops=366,642)

  • Index Cond: ((zulu_foxtrot = hotel_hotel.zulu_foxtrot) AND (seven_lima = 'hotel_zulu'::text))
  • Heap Fetches: 171
34. 61,919.592 61,919.592 ↑ 1.0 1,449 368,569

Seq Scan on uniform_papa three_kilo (cost=0.000..44.490 rows=1,449 width=11) (actual time=0.002..0.168 rows=1,449 loops=368,569)

35. 10,319.932 10,319.932 ↓ 0.0 0 368,569

Index Scan using delta_kilo on lima_victor xray_sierra (cost=0.430..2.480 rows=1 width=23) (actual time=0.028..0.028 rows=0 loops=368,569)

  • Index Cond: ((hotel_hotel.zulu_foxtrot = zulu_foxtrot) AND (seven_lima = 'victor_yankee'::text))
Planning time : 10.473 ms
Execution time : 284,119.058 ms