explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aojp

Settings
# exclusive inclusive rows x rows loops node
1. 0.072 3,973.105 ↓ 33.4 367 1

Unique (cost=259,849.950..259,860.530 rows=11 width=32) (actual time=3,972.619..3,973.105 rows=367 loops=1)

2.          

CTE seven

3. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.000..0.010 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=1)

4.          

CTE juliet

5. 0.206 7.963 ↓ 33.4 367 1

GroupAggregate (cost=285.700..285.950 rows=11 width=16) (actual time=7.719..7.963 rows=367 loops=1)

  • Group Key: five_foxtrot2.quebec_seven
6.          

Initplan (for GroupAggregate)

7. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on seven (cost=0.000..0.020 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)

8. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on seven lima_alpha (cost=0.000..0.020 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

9. 0.202 7.752 ↓ 33.4 367 1

Sort (cost=285.660..285.690 rows=11 width=32) (actual time=7.707..7.752 rows=367 loops=1)

  • Sort Key: five_foxtrot2.quebec_seven
  • Sort Method: quicksort Memory: 45kB
10. 0.135 7.550 ↓ 33.4 367 1

Nested Loop Left Join (cost=4.380..285.470 rows=11 width=32) (actual time=0.079..7.550 rows=367 loops=1)

11. 0.197 4.112 ↓ 33.4 367 1

Nested Loop Left Join (cost=3.950..192.490 rows=11 width=28) (actual time=0.076..4.112 rows=367 loops=1)

12. 0.416 1.346 ↓ 33.4 367 1

Hash Left Join (cost=3.510..99.410 rows=11 width=20) (actual time=0.070..1.346 rows=367 loops=1)

  • Hash Cond: ((five_foxtrot2.tango_zulu = oscar.tango_zulu) AND (five_foxtrot2.bravo_alpha = oscar.quebec_seven))
  • Filter: ((oscar.yankee_three five_romeo NULL) AND ((five_foxtrot2.bravo_alpha five_romeo NULL) OR (romeo_november(ARRAY[oscar.charlie_five], NULL::integer) && $2)))
13. 0.911 0.911 ↓ 6.3 367 1

Seq Scan on alpha four_xray (cost=0.000..95.590 rows=58 width=28) (actual time=0.026..0.911 rows=367 loops=1)

  • Filter: ((yankee_three five_romeo NULL) AND (tango_zulu = $1))
  • Rows Removed by Filter: 1603
14. 0.003 0.019 ↑ 1.2 5 1

Hash (cost=3.420..3.420 rows=6 width=20) (actual time=0.019..0.019 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
15. 0.016 0.016 ↑ 1.2 5 1

Seq Scan on oscar (cost=0.000..3.420 rows=6 width=20) (actual time=0.008..0.016 rows=5 loops=1)

  • Filter: (tango_zulu = $1)
  • Rows Removed by Filter: 77
16. 2.569 2.569 ↓ 0.0 0 367

Index Scan using romeo_six on hotel_romeo (cost=0.430..8.450 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=367)

  • Index Cond: (charlie_sierra = five_foxtrot2.charlie_sierra)
17. 3.303 3.303 ↓ 0.0 0 367

Index Scan using kilo_foxtrot on six hotel_victor (cost=0.430..8.450 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=367)

  • Index Cond: (three = five_foxtrot2.lima_november)
18. 0.331 3,973.033 ↓ 34.0 374 1

Merge Join (cost=259,563.990..259,574.540 rows=11 width=32) (actual time=3,972.618..3,973.033 rows=374 loops=1)

  • Merge Cond: (juliet.quebec_lima = alpha.quebec_seven)
19. 0.105 3,971.424 ↓ 34.0 374 1

Sort (cost=259,365.710..259,365.730 rows=11 width=24) (actual time=3,971.406..3,971.424 rows=374 loops=1)

  • Sort Key: juliet.quebec_lima
  • Sort Method: quicksort Memory: 46kB
20. 0.097 3,971.319 ↓ 34.0 374 1

Hash Right Join (cost=259,214.180..259,365.520 rows=11 width=24) (actual time=3,968.261..3,971.319 rows=374 loops=1)

  • Hash Cond: ((four_charlie(yankee_november1.tango_juliet)) = juliet.tango_juliet)
21. 3.191 3,957.087 ↑ 1.4 108 1

GroupAggregate (cost=259,119.280..259,268.480 rows=147 width=12) (actual time=3,954.107..3,957.087 rows=108 loops=1)

  • Group Key: six.xray
22. 0.545 3,953.896 ↓ 5.3 781 1

Sort (cost=259,119.280..259,119.650 rows=147 width=16) (actual time=3,953.844..3,953.896 rows=781 loops=1)

  • Sort Key: six.xray
  • Sort Method: quicksort Memory: 57kB
23. 1.430 3,953.351 ↓ 5.3 781 1

Nested Loop (cost=0.640..259,113.990 rows=147 width=16) (actual time=2.889..3,953.351 rows=781 loops=1)

24. 1,446.325 3,949.578 ↓ 5.3 781 1

Hash Right Join (cost=0.360..258,161.890 rows=147 width=20) (actual time=2.863..3,949.578 rows=781 loops=1)

  • Hash Cond: (six.xray = yankee_november1.tango_juliet)
25. 2,503.160 2,503.160 ↓ 1.0 8,937,205 1

Seq Scan on six (cost=0.000..224,663.680 rows=8,932,368 width=8) (actual time=0.011..2,503.160 rows=8,937,205 loops=1)

26. 0.045 0.093 ↓ 33.4 367 1

Hash (cost=0.220..0.220 rows=11 width=12) (actual time=0.093..0.093 rows=367 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
27. 0.048 0.048 ↓ 33.4 367 1

CTE Scan on juliet bravo_papa (cost=0.000..0.220 rows=11 width=12) (actual time=0.002..0.048 rows=367 loops=1)

28. 2.343 2.343 ↑ 1.0 1 781

Index Only Scan using golf on alpha november (cost=0.280..6.480 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=781)

  • Index Cond: (quebec_seven = yankee_november1.quebec_lima)
  • Heap Fetches: 369
29. 0.114 14.135 ↓ 34.0 374 1

Hash (cost=94.770..94.770 rows=11 width=24) (actual time=14.135..14.135 rows=374 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
30. 0.061 14.021 ↓ 34.0 374 1

Nested Loop Left Join (cost=0.560..94.770 rows=11 width=24) (actual time=7.772..14.021 rows=374 loops=1)

31. 8.088 8.088 ↓ 33.4 367 1

CTE Scan on juliet (cost=0.000..0.220 rows=11 width=16) (actual time=7.722..8.088 rows=367 loops=1)

32. 5.872 5.872 ↓ 0.0 0 367

Index Scan using india on kilo_echo (cost=0.560..8.580 rows=1 width=8) (actual time=0.014..0.016 rows=0 loops=367)

  • Index Cond: ((juliet.bravo_whiskey = three) AND (three = juliet.bravo_whiskey))
  • Filter: (juliet.tango_juliet = xray)
  • Rows Removed by Filter: 1
33. 0.553 1.278 ↑ 1.1 1,823 1

Sort (cost=198.280..203.200 rows=1,967 width=20) (actual time=1.193..1.278 rows=1,823 loops=1)

  • Sort Key: alpha.quebec_seven
  • Sort Method: quicksort Memory: 145kB
34. 0.725 0.725 ↓ 1.0 1,970 1

Seq Scan on alpha (cost=0.000..90.670 rows=1,967 width=20) (actual time=0.013..0.725 rows=1,970 loops=1)

Planning time : 1.176 ms
Execution time : 3,973.406 ms