explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hXnP : Optimization for: plan #a7e4

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.147 3,964.969 ↓ 5.0 10 1

Limit (cost=4,205.590..4,205.700 rows=2 width=301) (actual time=3,956.673..3,964.969 rows=10 loops=1)

2.          

CTE echo_yankee_november

3. 6.522 20.173 ↑ 1.0 233 1

GroupAggregate (cost=46.450..52.280 rows=233 width=467) (actual time=11.997..20.173 rows=233 loops=1)

  • Group Key: foxtrot_sierra1.quebec_seven
4. 3.859 13.651 ↓ 1.1 245 1

Sort (cost=46.450..47.030 rows=233 width=1,972) (actual time=11.950..13.651 rows=245 loops=1)

  • Sort Key: foxtrot_sierra1.quebec_seven
  • Sort Method: quicksort Memory: 207kB
5. 4.084 9.792 ↓ 1.1 245 1

Hash Left Join (cost=18.740..37.290 rows=233 width=1,972) (actual time=3.807..9.792 rows=245 loops=1)

  • Hash Cond: (foxtrot_sierra1.quebec_seven = three_sierra.xray_quebec)
6. 1.954 1.954 ↑ 1.0 233 1

Seq Scan on charlie_two echo_yankee_quebec (cost=0.000..15.640 rows=233 width=403) (actual time=0.018..1.954 rows=233 loops=1)

  • Filter: ((foxtrot_delta > zulu_golf()) OR (foxtrot_delta five_romeo NULL))
  • Rows Removed by Filter: 10
7. 1.052 3.754 ↑ 1.0 121 1

Hash (cost=17.220..17.220 rows=121 width=1,606) (actual time=3.754..3.754 rows=121 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 58kB
8. 1.682 2.702 ↑ 1.0 121 1

Hash Left Join (cost=11.350..17.220 rows=121 width=1,606) (actual time=0.210..2.702 rows=121 loops=1)

  • Hash Cond: (three_sierra.tango = golf_juliet2.quebec_seven)
9. 0.860 0.860 ↑ 1.0 121 1

Seq Scan on golf_november three_sierra (cost=0.000..4.210 rows=121 width=266) (actual time=0.022..0.860 rows=121 loops=1)

10. 0.079 0.160 ↑ 6.7 9 1

Hash (cost=10.600..10.600 rows=60 width=1,377) (actual time=0.160..0.160 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
11. 0.081 0.081 ↑ 6.7 9 1

Seq Scan on lima_tango romeo_two (cost=0.000..10.600 rows=60 width=1,377) (actual time=0.018..0.081 rows=9 loops=1)

12. 10.024 3,964.822 ↓ 3.7 11 1

GroupAggregate (cost=4,153.250..4,153.430 rows=3 width=301) (actual time=3,955.451..3,964.822 rows=11 loops=1)

  • Group Key: whiskey_quebec.romeo_hotel, whiskey_quebec.november_india
13. 8.858 3,954.798 ↓ 18.3 55 1

Sort (cost=4,153.250..4,153.260 rows=3 width=4,851) (actual time=3,954.070..3,954.798 rows=55 loops=1)

  • Sort Key: whiskey_quebec.romeo_hotel DESC, whiskey_quebec.november_india
  • Sort Method: external sort Disk: 4264kB
14. 7.567 3,945.940 ↓ 111.3 334 1

Nested Loop Anti Join (cost=941.220..4,153.230 rows=3 width=4,851) (actual time=52.829..3,945.940 rows=334 loops=1)

15. 13.524 3,935.367 ↓ 111.3 334 1

Nested Loop Left Join (cost=941.080..4,151.790 rows=3 width=4,851) (actual time=52.789..3,935.367 rows=334 loops=1)

  • Join Filter: (juliet.quebec_seven = hotel.india_charlie)
  • Rows Removed by Join Filter: 996
16. 125.531 3,911.823 ↓ 111.3 334 1

Nested Loop Left Join (cost=905.880..4,089.990 rows=3 width=4,627) (actual time=51.700..3,911.823 rows=334 loops=1)

  • Join Filter: (whiskey_quebec.november_india = lima_yankee.quebec_seven)
  • Rows Removed by Join Filter: 17033
  • Filter: ((lima_yankee.quebec_seven five_romeo NULL) OR (lima_yankee.foxtrot_quebec five_romeo NOT NULL))
17. 7.568 3,670.728 ↓ 111.3 334 1

Nested Loop Left Join (cost=905.880..4,084.060 rows=3 width=4,142) (actual time=49.786..3,670.728 rows=334 loops=1)

  • Filter: ((echo_six.quebec_seven five_romeo NULL) OR (echo_six.foxtrot_quebec five_romeo NOT NULL))
18. 22.607 3,659.152 ↓ 111.3 334 1

Nested Loop (cost=905.610..4,081.240 rows=3 width=3,560) (actual time=49.711..3,659.152 rows=334 loops=1)

  • Join Filter: ((quebec_sierra.quebec_seven)::text = ANY (xray_whiskey.kilo_charlie))
  • Rows Removed by Join Filter: 1772
19. 7.183 3,619.229 ↓ 234.0 234 1

Nested Loop Left Join (cost=905.610..4,069.140 rows=1 width=2,196) (actual time=49.408..3,619.229 rows=234 loops=1)

  • Filter: ((juliet.quebec_seven five_romeo NULL) OR (november_mike.quebec_seven five_romeo NOT NULL))
  • Rows Removed by Filter: 38
20. 437.947 3,594.910 ↓ 272.0 272 1

Nested Loop Left Join (cost=905.190..4,061.190 rows=1 width=719) (actual time=49.340..3,594.910 rows=272 loops=1)

  • Join Filter: ((four.xray_quebec)::bpchar = november_mike.quebec_seven)
  • Rows Removed by Join Filter: 63188
21. 1,337.743 2,669.811 ↓ 272.0 272 1

Nested Loop Left Join (cost=905.190..4,053.610 rows=1 width=436) (actual time=24.088..2,669.811 rows=272 loops=1)

  • Join Filter: (whiskey_quebec.november_india = (four.november_india)::bpchar)
  • Rows Removed by Join Filter: 192733
22. 4.428 35.138 ↓ 170.0 170 1

Nested Loop Left Join (cost=905.190..4,004.080 rows=1 width=399) (actual time=5.880..35.138 rows=170 loops=1)

23. 6.901 26.630 ↓ 170.0 170 1

Hash Join (cost=904.770..3,996.340 rows=1 width=227) (actual time=5.833..26.630 rows=170 loops=1)

  • Hash Cond: (whiskey_quebec.november_india = xray_whiskey.november_india)
24. 14.001 14.321 ↓ 3.5 749 1

Seq Scan on yankee_juliet whiskey_quebec (cost=20.950..3,111.710 rows=213 width=214) (actual time=0.391..14.321 rows=749 loops=1)

  • Filter: (charlie_victor AND (NOT mike_bravo) AND (NOT (mike_mike delta 2)) AND (NOT (mike_mike delta 3)) AND ((xray_golf)::text = ANY ('quebec_papa'::text[])))
  • Rows Removed by Filter: 85105
25.          

SubPlan (for Seq Scan)

26. 0.058 0.238 ↓ 0.0 0 1

GroupAggregate (cost=8.170..8.190 rows=1 width=148) (actual time=0.238..0.238 rows=0 loops=1)

  • Group Key: victor_romeo.november_india
  • Filter: (five_two(victor_romeo.november_india) >= 5)
  • Rows Removed by Filter: 3
27. 0.110 0.180 ↓ 6.0 6 1

Sort (cost=8.170..8.180 rows=1 width=148) (actual time=0.140..0.180 rows=6 loops=1)

  • Sort Key: victor_romeo.november_india
  • Sort Method: quicksort Memory: 25kB
28. 0.070 0.070 ↓ 6.0 6 1

Index Scan using zulu_alpha on alpha_five victor_romeo (cost=0.140..8.160 rows=1 width=148) (actual time=0.026..0.070 rows=6 loops=1)

  • Index Cond: (zulu_lima = 'three_tango'::bpchar)
  • Filter: ("india_mike" <= 2)
  • Rows Removed by Filter: 14
29. 0.082 0.082 ↓ 0.0 0 1

Seq Scan on sierra (cost=0.000..12.200 rows=220 width=148) (actual time=0.082..0.082 rows=0 loops=1)

30. 2.567 5.408 ↑ 1.1 336 1

Hash (cost=879.210..879.210 rows=369 width=50) (actual time=5.408..5.408 rows=336 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 56kB
31. 2.841 2.841 ↑ 1.1 336 1

Bitmap Heap Scan on victor_xray xray_whiskey (cost=22.860..879.210 rows=369 width=50) (actual time=0.112..2.841 rows=336 loops=1)

  • Recheck Cond: ('two_xray'::text[] && kilo_charlie)
  • Heap Blocks: exact=203me=0.076..0.076 rows=336 loops=1) -> Bitmap Index Scan on idx_content_categories (cost=0.00..22.77 rows=369 width=0) (actual ti--More--
  • Index Cond: ('two_xray'::text[] && kilo_charlie)
32. 4.080 4.080 ↑ 1.0 1 170

Index Scan using uniform_kilo on whiskey_romeo five_victor (cost=0.420..7.720 rows=1 width=209) (actual time=0.017..0.024 rows=1 loops=170)

  • Index Cond: (whiskey_quebec.november_india = november_india)
33. 1,296.930 1,296.930 ↑ 1.0 1,135 170

Seq Scan on yankee_hotel four (cost=0.000..35.350 rows=1,135 width=74) (actual time=0.010..7.629 rows=1,135 loops=170)

34. 487.152 487.152 ↑ 1.0 233 272

CTE Scan on echo_yankee_november november_mike (cost=0.000..4.660 rows=233 width=320) (actual time=0.052..1.791 rows=233 loops=272)

35. 17.136 17.136 ↑ 1.0 1 272

Index Scan using alpha_zulu on kilo_zulu juliet (cost=0.420..7.950 rows=1 width=1,477) (actual time=0.057..0.063 rows=1 loops=272)

  • Index Cond: (whiskey_quebec.november_india = quebec_seven)
36. 17.316 17.316 ↑ 6.7 9 234

Seq Scan on lima_tango quebec_sierra (cost=0.000..10.600 rows=60 width=1,377) (actual time=0.010..0.074 rows=9 loops=234)

37. 4.008 4.008 ↓ 0.0 0 334

Index Scan using mike_delta on seven_six echo_six (cost=0.270..0.930 rows=1 width=728) (actual time=0.011..0.012 rows=0 loops=334)

  • Index Cond: (whiskey_quebec.november_india = quebec_seven)
38. 115.107 115.564 ↑ 1.0 51 334

Materialize (cost=0.000..3.760 rows=51 width=627) (actual time=0.007..0.346 rows=51 loops=334)

39. 0.457 0.457 ↑ 1.0 51 1

Seq Scan on uniform_bravo lima_yankee (cost=0.000..3.510 rows=51 width=627) (actual time=0.024..0.457 rows=51 loops=1)

40. 9.039 10.020 ↑ 126.7 3 334

Materialize (cost=35.200..45.650 rows=380 width=372) (actual time=0.010..0.030 rows=3 loops=334)

41. 0.063 0.981 ↑ 126.7 3 1

Subquery Scan on hotel (cost=35.200..43.750 rows=380 width=372) (actual time=0.908..0.981 rows=3 loops=1)

42. 0.458 0.918 ↑ 126.7 3 1

HashAggregate (cost=35.200..39.950 rows=380 width=216) (actual time=0.885..0.918 rows=3 loops=1)

  • Group Key: alpha_foxtrot.quebec_seven
43. 0.251 0.460 ↑ 25.3 15 1

Hash Right Join (cost=18.550..33.300 rows=380 width=588) (actual time=0.145..0.460 rows=15 loops=1)

  • Hash Cond: (golf_juliet1.two_charlie = alpha_foxtrot.quebec_seven)
44. 0.127 0.127 ↑ 13.3 15 1

Seq Scan on foxtrot_kilo zulu_zulu (cost=0.000..12.000 rows=200 width=420) (actual time=0.021..0.127 rows=15 loops=1)

45. 0.047 0.082 ↑ 126.7 3 1

Hash (cost=13.800..13.800 rows=380 width=184) (actual time=0.082..0.082 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.035 0.035 ↑ 126.7 3 1

Seq Scan on alpha_foxtrot (cost=0.000..13.800 rows=380 width=184) (actual time=0.014..0.035 rows=3 loops=1)

47. 3.006 3.006 ↓ 0.0 0 334

Index Scan using oscar on bravo uniform_seven (cost=0.140..0.400 rows=1 width=148) (actual time=0.009..0.009 rows=0 loops=334)

  • Index Cond: (november_india = whiskey_quebec.november_india)
  • Filter: ((zulu_lima = 'three_tango'::bpchar) AND (seven_papa < (zulu_golf() - 'seven_oscar'::interval)))
Planning time : 9.610 ms
Execution time : 3,967.631 ms