explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 401

Settings
# exclusive inclusive rows x rows loops node
1. 0.265 99,488.192 ↑ 1.0 1 1

Aggregate (cost=36,253.270..36,253.300 rows=1 width=8) (actual time=99,488.192..99,488.192 rows=1 loops=1)

2. 0.742 99,487.927 ↓ 3,924.0 3,924 1

Group (cost=36,253.230..36,253.240 rows=1 width=16) (actual time=99,486.898..99,487.927 rows=3,924 loops=1)

  • Group Key: juliet_xray.quebec_seven, kilo_foxtrot.quebec_seven, tango.mike_four
3. 6.741 99,487.185 ↓ 3,925.0 3,925 1

Sort (cost=36,253.230..36,253.230 rows=1 width=16) (actual time=99,486.896..99,487.185 rows=3,925 loops=1)

  • Sort Key: juliet_xray.quebec_seven, kilo_foxtrot.quebec_seven, tango.mike_four
  • Sort Method: quicksort Memory: 280kB
4. 15.281 99,480.444 ↓ 3,925.0 3,925 1

Nested Loop Left Join (cost=6.010..36,253.220 rows=1 width=16) (actual time=68.823..99,480.444 rows=3,925 loops=1)

  • Filter: (1 = CASE WHEN ((juliet_xray.quebec_five = ANY ('foxtrot_seven'::integer[])) AND ((CASE WHEN (seven_alpha.mike_romeo five_romeo NULL) THEN 1 ELSE 0 END) = 1)) THEN 1 WHEN ((juliet_xray.quebec_five <> november_november ('foxtrot_seven'::integer[])) AND (seven_alpha.mike_romeo five_romeo NULL)) THEN 1 ELSE 0 END)
5. 9.856 1,540.338 ↓ 3,925.0 3,925 1

Nested Loop Left Join (cost=3.160..36,232.650 rows=1 width=28) (actual time=31.915..1,540.338 rows=3,925 loops=1)

6. 16.794 1,522.632 ↓ 3,925.0 3,925 1

Nested Loop (cost=3.010..36,232.320 rows=1 width=28) (actual time=31.908..1,522.632 rows=3,925 loops=1)

  • Join Filter: (juliet_xray.quebec_five = india_foxtrot.quebec_seven)
  • Rows Removed by Join Filter: 11,803
7. 8.358 1,497.988 ↓ 3,925.0 3,925 1

Nested Loop (cost=3.010..36,227.320 rows=1 width=28) (actual time=31.899..1,497.988 rows=3,925 loops=1)

  • Join Filter: (tango.three_alpha = kilo_foxtrot.quebec_seven)
8. 11.005 1,466.080 ↓ 3,925.0 3,925 1

Nested Loop (cost=2.740..36,226.970 rows=1 width=36) (actual time=31.868..1,466.080 rows=3,925 loops=1)

  • Join Filter: ((tango.three_alpha = juliet_xray.three_alpha) AND (tango.quebec_seven = juliet_xray.delta))
9. 21.858 289.350 ↓ 3,925.0 3,925 1

Nested Loop (cost=2.450..36,215.690 rows=1 width=36) (actual time=31.451..289.350 rows=3,925 loops=1)

  • Join Filter: (two_romeo.three_alpha = tango.three_alpha)
10. 12.386 163.794 ↓ 283.3 17,283 1

Hash Join (cost=2.030..36,152.770 rows=61 width=20) (actual time=15.012..163.794 rows=17,283 loops=1)

  • Hash Cond: (november_six.three_alpha = two_romeo.three_alpha)
11. 137.361 137.361 ↑ 1.0 18,256 1

Seq Scan on india_juliet november_six (cost=0.000..36,077.940 rows=18,923 width=12) (actual time=0.947..137.361 rows=18,256 loops=1)

  • Filter: ((hotel = 3,482) AND (two_delta = 16))
  • Rows Removed by Filter: 382,160
12. 0.027 14.047 ↓ 171.0 171 1

Hash (cost=2.000..2.000 rows=1 width=8) (actual time=14.046..14.047 rows=171 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
13. 14.020 14.020 ↓ 171.0 171 1

Function Scan on xray_echo two_romeo (cost=0.250..2.000 rows=1 width=8) (actual time=13.984..14.020 rows=171 loops=1)

  • Filter: ((hotel = 3,482) AND (two_papa = 0))
  • Rows Removed by Filter: 195
14. 103.698 103.698 ↓ 0.0 0 17,283

Index Scan using sierra on juliet_alpha tango (cost=0.420..1.000 rows=1 width=20) (actual time=0.006..0.006 rows=0 loops=17,283)

  • Index Cond: ((hotel = 3,482) AND (quebec_seven = november_six.delta))
  • Filter: ((xray_lima five_romeo TRUE) AND (three_golf five_romeo FALSE) AND (mike_victor five_romeo NOT NULL) AND (november_six.three_alpha = three_alpha) AND (((five_foxtrot -> 'mike_kilo'::text) = 'kilo_sierra'::charlie_five) OR ((five_foxtrot -> 'mike_kilo'::text) five_romeo NULL)))
  • Rows Removed by Filter: 1
15. 1,165.725 1,165.725 ↑ 1.0 1 3,925

Index Scan using yankee_golf on papa_mike juliet_xray (cost=0.290..11.250 rows=1 width=20) (actual time=0.087..0.297 rows=1 loops=3,925)

  • Index Cond: (three_alpha = november_six.three_alpha)
  • Filter: ((mike_romeo five_romeo NULL) AND (india_two five_romeo NULL) AND (hotel = 3,482) AND (november_six.delta = delta))
  • Rows Removed by Filter: 753
16. 23.550 23.550 ↑ 1.0 1 3,925

Index Only Scan using whiskey on two_bravo kilo_foxtrot (cost=0.270..0.320 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=3,925)

  • Index Cond: ((hotel = 3,482) AND (quebec_seven = juliet_xray.three_alpha))
  • Heap Fetches: 3,925
17. 7.850 7.850 ↑ 12.0 4 3,925

Seq Scan on bravo india_foxtrot (cost=0.000..3.440 rows=48 width=4) (actual time=0.002..0.002 rows=4 loops=3,925)

18. 7.850 7.850 ↓ 0.0 0 3,925

Index Scan using four on three_uniform seven_uniform (cost=0.150..0.290 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=3,925)

  • Index Cond: ((hotel = juliet_xray.hotel) AND (hotel = 3,482))
  • Filter: ((oscar_charlie five_romeo NULL) AND (mike_romeo five_romeo NULL) AND (kilo_november = 4) AND (three_alpha = juliet_xray.three_alpha) AND (foxtrot_zulu_bravo = juliet_xray.quebec_seven))
19. 0.000 97,924.825 ↓ 0.0 0 3,925

Nested Loop (cost=2.840..20.530 rows=1 width=24) (actual time=24.949..24.949 rows=0 loops=3,925)

  • Join Filter: (juliet_xray1.three_alpha = charlie_zulu1.three_alpha)
20. 1,201.050 3,446.150 ↓ 171.0 171 3,925

Nested Loop (cost=1.530..4.320 rows=1 width=16) (actual time=0.009..0.878 rows=171 loops=3,925)

21. 231.575 231.575 ↓ 171.0 171 3,925

Function Scan on xray_echo five_uniform (cost=0.250..2.000 rows=1 width=8) (actual time=0.004..0.059 rows=171 loops=3,925)

  • Filter: ((hotel = 3,482) AND (two_papa = 0))
  • Rows Removed by Filter: 195
22. 1,342.350 2,013.525 ↑ 1.0 1 671,175

Bitmap Heap Scan on two_bravo juliet_juliet (cost=1.280..2.320 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=671,175)

  • Recheck Cond: (quebec_seven = charlie_zulu1.three_alpha)
  • Filter: (hotel = 3,482)
  • Heap Blocks: exact=894,900
23. 671.175 671.175 ↑ 1.0 1 671,175

Bitmap Index Scan on six (cost=0.000..1.280 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=671,175)

  • Index Cond: (quebec_seven = charlie_zulu1.three_alpha)
24. 3,983.875 94,635.675 ↓ 0.0 0 671,175

Hash Join (cost=1.310..16.180 rows=1 width=32) (actual time=0.141..0.141 rows=0 loops=671,175)

  • Hash Cond: (juliet_xray1.three_alpha = seven_alpha.three_alpha)
  • Join Filter: ((juliet_xray1.quebec_five = juliet_xray.quebec_five) AND CASE WHEN (juliet_xray1.quebec_five = ANY ('foxtrot_zulu_quebec'::integer[])) THEN (seven_alpha.alpha = 1) WHEN (juliet_xray1.quebec_five = 13) THEN (seven_alpha.alpha = 3) ELSE NULL::boolean END)
  • Rows Removed by Join Filter: 0
25. 90,608.625 90,608.625 ↓ 1.4 65 671,175

Index Scan using yankee_golf on papa_mike kilo_victor (cost=0.290..14.940 rows=47 width=12) (actual time=0.036..0.135 rows=65 loops=671,175)

  • Index Cond: (three_alpha = oscar_mike1.quebec_seven)
  • Filter: ((hotel = 3,482) AND (quebec_five = ANY ('foxtrot_seven'::integer[])))
  • Rows Removed by Filter: 293
26. 7.850 43.175 ↓ 0.0 0 3,925

Hash (cost=0.990..0.990 rows=1 width=24) (actual time=0.011..0.011 rows=0 loops=3,925)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
27. 35.325 35.325 ↓ 0.0 0 3,925

Index Scan using papa_four on yankee_oscar seven_alpha (cost=0.140..0.990 rows=1 width=24) (actual time=0.009..0.009 rows=0 loops=3,925)

  • Index Cond: ((hotel = juliet_xray.hotel) AND (hotel = 3,482))
  • Filter: ((mike_romeo five_romeo NULL) AND (three_alpha = juliet_xray.three_alpha))
  • Rows Removed by Filter: 20
Planning time : 9.409 ms
Execution time : 99,489.678 ms