explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CHoc

Settings
# exclusive inclusive rows x rows loops node
1. 0.052 110,414.664 ↑ 1.0 20 1

Limit (cost=1,317,494.520..1,317,495.970 rows=20 width=269) (actual time=110,414.580..110,414.664 rows=20 loops=1)

2. 0.027 110,414.612 ↑ 6,593.1 20 1

Unique (cost=1,317,494.520..1,325,735.890 rows=131,862 width=269) (actual time=110,414.576..110,414.612 rows=20 loops=1)

3. 5,050.767 110,414.585 ↑ 6,593.1 20 1

Sort (cost=1,317,494.520..1,317,824.170 rows=131,862 width=269) (actual time=110,414.573..110,414.585 rows=20 loops=1)

  • Sort Key: sierra_foxtrot_papa.four_victor, sierra_foxtrot_papa.hotel_three, sierra_foxtrot_papa.zulu_golf, sierra_foxtrot_papa.foxtrot_seven, sierra_foxtrot_papa.juliet_charlie, sierra_foxtrot_papa.hotel_mike, sierra_foxtrot_papa.romeo_juliet, sierra_foxtrot_papa.echo, sierra_foxtrot_papa.hotel_four, sierra_foxtrot_papa.whiskey_hotel, sierra_foxtrot_papa.golf_november, sierra_foxtrot_papa.foxtrot_lima, sierra_foxtrot_papa.kilo_charlie, sierra_foxtrot_papa.six_charlie, sierra_foxtrot_papa.zulu_delta, sierra_foxtrot_papa.sierra_foxtrot_xray, (lima(seven_golf_victor.golf_november, 'romeo_quebec'::character varying)), five_hotel.four_papa, (six_three(five_hotel.bravo_three, five_hotel.alpha, five_hotel.delta_three, five_hotel.kilo_kilo)), xray_oscar.sierra_bravo, xray_oscar.victor, delta_hotel.juliet_five, (CASE WHEN ((bravo_papa.charlie_november > 0) AND (bravo_papa.whiskey_xray > 0)) THEN 'quebec_hotel'::text WHEN ((bravo_papa.charlie_november > 0) AND (bravo_papa.whiskey_xray <= 0)) THEN 'india_seven'::text WHEN ((bravo_papa.charlie_november <= 0) AND (bravo_papa.whiskey_xray > 0)) THEN 'five_india'::text ELSE NULL::text END), seven_golf_victor.hotel_sierra
  • Sort Method: external merge Disk: 123640kB
4. 10,100.737 105,363.818 ↓ 4.9 648,162 1

Merge Right Join (cost=1,141,467.510..1,306,280.530 rows=131,862 width=269) (actual time=59,799.872..105,363.818 rows=648,162 loops=1)

  • Merge Cond: ((november.four_victor)::text = (sierra_foxtrot_papa.four_victor)::text)
5. 2,240.923 18,612.765 ↑ 1.0 1,625,261 1

GroupAggregate (cost=512,898.510..555,517.510 rows=1,704,760 width=90) (actual time=14,169.031..18,612.765 rows=1,625,261 loops=1)

  • Group Key: november.four_victor, november.juliet_charlie, november.seven_golf_foxtrot, seven_golf_victor.golf_november, seven_golf_victor.hotel_sierra
6. 6,947.717 16,371.842 ↑ 1.0 1,689,320 1

Sort (cost=512,898.510..517,160.410 rows=1,704,760 width=17) (actual time=14,169.019..16,371.842 rows=1,689,320 loops=1)

  • Sort Key: november.four_victor, november.juliet_charlie, november.seven_golf_foxtrot, seven_golf_victor.golf_november, seven_golf_victor.hotel_sierra
  • Sort Method: external merge Disk: 51112kB
7. 2,329.374 9,424.125 ↑ 1.0 1,689,320 1

Hash Join (cost=228,517.060..301,484.660 rows=1,704,760 width=17) (actual time=6,271.622..9,424.125 rows=1,689,320 loops=1)

  • Hash Cond: (november.seven_echo = seven_golf_victor.five_kilo)
8. 853.810 853.810 ↓ 1.0 1,707,052 1

Seq Scan on delta_uniform november (cost=0.000..31,343.600 rows=1,704,760 width=19) (actual time=7.514..853.810 rows=1,707,052 loops=1)

9. 2,296.960 6,240.941 ↓ 1.0 4,400,902 1

Hash (cost=156,399.360..156,399.360 rows=4,395,736 width=6) (actual time=6,240.940..6,240.941 rows=4,400,902 loops=1)

  • Buckets: 2097152 Batches: 8 Memory Usage: 37870kB
10. 3,943.981 3,943.981 ↓ 1.0 4,400,902 1

Seq Scan on mike_five_six seven_golf_victor (cost=0.000..156,399.360 rows=4,395,736 width=6) (actual time=9.085..3,943.981 rows=4,400,902 loops=1)

11. 744.173 76,650.316 ↓ 9.0 648,161 1

Materialize (cost=628,569.000..694,690.000 rows=71,962 width=237) (actual time=45,629.657..76,650.316 rows=648,161 loops=1)

12. 695.216 75,906.143 ↓ 4.1 295,244 1

Nested Loop (cost=628,569.000..694,510.090 rows=71,962 width=237) (actual time=45,629.647..75,906.143 rows=295,244 loops=1)

13. 1,234.544 48,934.211 ↓ 4.1 295,244 1

Merge Left Join (cost=628,568.570..644,787.650 rows=71,962 width=204) (actual time=45,617.644..48,934.211 rows=295,244 loops=1)

  • Merge Cond: ((sierra_foxtrot_papa.four_victor)::text = (bravo_papa.four_victor)::text)
14. 1,310.478 40,203.887 ↓ 4.1 295,244 1

Sort (cost=495,362.540..495,542.440 rows=71,962 width=172) (actual time=39,839.374..40,203.887 rows=295,244 loops=1)

  • Sort Key: sierra_foxtrot_papa.four_victor
  • Sort Method: external merge Disk: 45784kB
15. 261.426 38,893.409 ↓ 4.1 295,244 1

Hash Left Join (cost=437,453.860..489,557.020 rows=71,962 width=172) (actual time=37,194.813..38,893.409 rows=295,244 loops=1)

  • Hash Cond: ((delta_hotel.victor)::text = (xray_oscar.victor)::text)
16. 253.234 38,631.800 ↓ 4.1 295,244 1

Hash Left Join (cost=437,447.340..489,357.610 rows=71,962 width=156) (actual time=37,194.612..38,631.800 rows=295,244 loops=1)

  • Hash Cond: (india_lima.quebec_kilo_foxtrot = delta_hotel.quebec_kilo_foxtrot)
17. 284.175 38,377.035 ↓ 4.1 295,244 1

Hash Left Join (cost=437,383.570..489,104.410 rows=71,962 width=140) (actual time=37,193.059..38,377.035 rows=295,244 loops=1)

  • Hash Cond: ((sierra_foxtrot_papa.zulu_golf)::text = (india_lima.romeo_xray)::text)
18. 532.093 37,827.665 ↓ 4.1 295,244 1

Hash Join (cost=430,053.230..481,585.170 rows=71,962 width=136) (actual time=36,925.640..37,827.665 rows=295,244 loops=1)

  • Hash Cond: ((sierra_foxtrot_papa.zulu_golf)::text = (kilo_sierra.romeo_xray)::text)
19. 382.906 382.906 ↑ 1.0 295,248 1

Seq Scan on quebec_kilo_echo sierra_foxtrot_papa (cost=0.000..26,913.380 rows=297,738 width=117) (actual time=7.210..382.906 rows=295,248 loops=1)

  • Filter: (quebec_six = 210)
  • Rows Removed by Filter: 635124
20. 1,659.674 36,912.666 ↑ 1.0 1,707,370 1

Hash (cost=395,295.000..395,295.000 rows=1,711,139 width=34) (actual time=36,912.665..36,912.666 rows=1,707,370 loops=1)

  • Buckets: 1048576 Batches: 4 Memory Usage: 36525kB
21. 28,250.951 35,252.992 ↑ 1.0 1,707,370 1

Bitmap Heap Scan on charlie_six kilo_sierra (cost=52,329.760..395,295.000 rows=1,711,139 width=34) (actual time=7,061.948..35,252.992 rows=1,707,370 loops=1)

  • Recheck Cond: (five_four = 9)
  • Heap Blocks: exact=312955
22. 7,002.041 7,002.041 ↓ 1.0 1,722,341 1

Bitmap Index Scan on golf_whiskey (cost=0.000..51,901.970 rows=1,711,139 width=0) (actual time=7,002.041..7,002.041 rows=1,722,341 loops=1)

  • Index Cond: (five_four = 9)
23. 115.145 265.195 ↓ 1.0 198,569 1

Hash (cost=4,851.260..4,851.260 rows=198,326 width=20) (actual time=265.195..265.195 rows=198,569 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 12132kB
24. 150.050 150.050 ↓ 1.0 198,569 1

Seq Scan on golf_quebec india_lima (cost=0.000..4,851.260 rows=198,326 width=20) (actual time=0.027..150.050 rows=198,569 loops=1)

25. 0.718 1.531 ↑ 1.0 1,501 1

Hash (cost=45.010..45.010 rows=1,501 width=24) (actual time=1.531..1.531 rows=1,501 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 102kB
26. 0.813 0.813 ↑ 1.0 1,501 1

Seq Scan on xray_two delta_hotel (cost=0.000..45.010 rows=1,501 width=24) (actual time=0.017..0.813 rows=1,501 loops=1)

27. 0.097 0.183 ↑ 1.0 201 1

Hash (cost=4.010..4.010 rows=201 width=25) (actual time=0.182..0.183 rows=201 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
28. 0.086 0.086 ↑ 1.0 201 1

Seq Scan on golf_echo xray_oscar (cost=0.000..4.010 rows=201 width=25) (actual time=0.006..0.086 rows=201 loops=1)

29. 968.796 7,495.780 ↓ 1.7 852,515 1

Subquery Scan on bravo_papa (cost=133,206.030..147,378.290 rows=515,355 width=41) (actual time=5,751.585..7,495.780 rows=852,515 loops=1)

30. 3,495.265 6,526.984 ↓ 1.7 852,515 1

Sort (cost=133,206.030..134,494.420 rows=515,355 width=25) (actual time=5,751.580..6,526.984 rows=852,515 loops=1)

  • Sort Key: juliet_six.four_victor
  • Sort Method: external merge Disk: 35040kB
31. 1,786.765 3,031.719 ↓ 1.7 852,515 1

HashAggregate (cost=79,157.640..84,311.190 rows=515,355 width=25) (actual time=2,520.144..3,031.719 rows=852,515 loops=1)

  • Group Key: juliet_six.four_victor
32. 1,244.954 1,244.954 ↑ 1.0 1,934,031 1

Seq Scan on juliet_six (cost=0.000..64,574.560 rows=1,944,411 width=17) (actual time=3.404..1,244.954 rows=1,934,031 loops=1)

  • Filter: ((india_oscar five_romeo NOT NULL) OR (golf_romeo five_romeo NOT NULL))
  • Rows Removed by Filter: 1097178
33. 26,276.716 26,276.716 ↑ 1.0 1 295,244

Index Scan using mike_five_golf on romeo_victor five_hotel (cost=0.430..0.690 rows=1 width=48) (actual time=0.089..0.089 rows=1 loops=295,244)

  • Index Cond: ((four_papa)::text = (kilo_sierra.four_papa)::text)
Planning time : 537.981 ms
Execution time : 110,569.247 ms