explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CuLS : Optimization for: plan #q2gA

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.012 760.010 ↑ 1.0 100 1

Limit (cost=24,141.90..24,144.40 rows=100 width=1,660) (actual time=759.976..760.010 rows=100 loops=1)

  • loops=1)
  • rows=142,718 loops=1)
  • loops=1)
  • COALESCE(at_ward.wardname), COALESCE(at_ward.districtname), COALESCE(at_ward.x_min), COALESCE(at_ward.x_max), COALESCE(at_ward.y_min), COALESCE(at_ward.y_max), _rank_quality_restaurants_count_metric_without_uncovered_1._rank_quality_restaurants_count_metric_without_uncovered_1, _percentile_quality_restaurants_count_metric_without_uncovered_._percentile_quality_restaurants_count_metric_without_uncovered_
2. 0.087 759.998 ↑ 2.0 100 1

WindowAgg (cost=24,141.90..24,146.90 rows=200 width=1,660) (actual time=759.975..759.998 rows=100 loops=1)

3. 0.028 759.911 ↑ 1.6 128 1

Subquery Scan on inner (cost=24,141.90..24,144.40 rows=200 width=1,652) (actual time=759.876..759.911 rows=128 loops=1)

4. 0.093 759.883 ↑ 1.6 128 1

Sort (cost=24,141.90..24,142.40 rows=200 width=1,668) (actual time=759.874..759.883 rows=128 loops=1)

  • Sort Key: quality_restaurants_count_metric_without_uncovered.quality_restaurants_count_metric_without_uncovered DESC NULLS LAST
  • Sort Method: quicksort Memory: 59kB
5.          

CTE quality_restaurants_count_metric_without_uncovered

6. 0.336 268.589 ↑ 1.6 128 1

Sort (cost=8,019.61..8,020.11 rows=200 width=18) (actual time=268.580..268.589 rows=128 loops=1)

  • Sort Key: ward_1.wardcode
  • Sort Method: quicksort Memory: 35kB
7. 0.172 268.253 ↑ 1.6 128 1

HashAggregate (cost=8,009.96..8,011.96 rows=200 width=18) (actual time=268.235..268.253 rows=128 loops=1)

  • Group Key: ward_1.wardcode
8. 82.041 268.081 ↑ 12.9 633 1

HashAggregate (cost=7,805.96..7,887.56 rows=8,160 width=14) (actual time=267.927..268.081 rows=633 loops=1)

  • Group Key: ward_1.wardcode, postcodes_zones_1.restaurant_id
  • -> Hash Join (cost=3,073.85..7024.94 rows=156,204 width=14) (actual time=35.632..228.460 rows=142,718
  • Hash Cond: ((postcodes_1.wardcode)::text = (ward_1.wardcode)::text)
9. 127.217 185.967 ↑ 1.1 142,718 1

Hash Join (cost=3,067.25..6,597.45 rows=156,204 width=14) (actual time=35.533..185.967 rows=142,718 loops=1)

  • Hash Cond: ((postcodes_point_dwh_1.postcode2)::text = (postcodes_1.postcode)::text)
  • -> Hash Join (cost=1,179.95..4300.07 rows=156,204 width=12) (actual time=16.088..111.731
  • Hash Cond: ((postcodes_zones_1.postcode)::text = (postcodes_point_dwh_1.postcode)::text)
10. 23.742 23.742 ↑ 1.0 156,204 1

Seq Scan on postcodes_zones_1 (cost=0.00..2,710.04 rows=156,204 width=11) (actual time=0.007..23.742 rows=156,204 loops=1)

11. 9.707 15.802 ↑ 1.0 38,309 1

Hash (cost=701.09..701.09 rows=38,309 width=16) (actual time=15.802..15.802 rows=38,309 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,309kB
12. 6.095 6.095 ↑ 1.0 38,309 1

Seq Scan on postcodes_point_dwh_1 (cost=0.00..701.09 rows=38,309 width=16) (actual time=0.009..6.095 rows=38,309 loops=1)

13. 9.958 19.206 ↑ 1.0 40,591 1

Hash (cost=1,379.91..1,379.91 rows=40,591 width=18) (actual time=19.205..19.206 rows=40,591 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,495kB
14. 9.248 9.248 ↑ 1.0 40,591 1

Seq Scan on postcodes_1 (cost=0.00..1,379.91 rows=40,591 width=18) (actual time=0.009..9.248 rows=40,591 loops=1)

15. 0.035 0.073 ↑ 1.0 160 1

Hash (cost=4.60..4.60 rows=160 width=10) (actual time=0.073..0.073 rows=160 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
16. 0.038 0.038 ↑ 1.0 160 1

Seq Scan on ward_1 (cost=0.00..4.60 rows=160 width=10) (actual time=0.012..0.038 rows=160 loops=1)

17.          

CTE _rank_quality_restaurants_count_metric_without_uncovered_1

18. 0.491 245.659 ↑ 1.6 128 1

Sort (cost=8,019.61..8,020.11 rows=200 width=18) (actual time=245.651..245.659 rows=128 loops=1)

  • Sort Key: ward_1_1.wardcode
  • Sort Method: quicksort Memory: 35kB
19. 0.201 245.168 ↑ 1.6 128 1

HashAggregate (cost=8,009.96..8,011.96 rows=200 width=18) (actual time=245.145..245.168 rows=128 loops=1)

  • Group Key: ward_1_1.wardcode
20. 80.503 244.967 ↑ 12.9 633 1

HashAggregate (cost=7,805.96..7,887.56 rows=8,160 width=14) (actual time=244.762..244.967 rows=633 loops=1)

  • Group Key: ward_1_1.wardcode, postcodes_zones_1_1.restaurant_id
  • -> Hash Join (cost=3,073.85..7024.94 rows=156,204 width=14) (actual time=31.361..204.907 rows=142,718
  • Hash Cond: ((postcodes_1_1.wardcode)::text = (ward_1_1.wardcode)::text)
21. 52.874 164.392 ↑ 1.1 142,718 1

Hash Join (cost=3,067.25..6,597.45 rows=156,204 width=14) (actual time=31.273..164.392 rows=142,718 loops=1)

  • Hash Cond: ((postcodes_point_dwh_1_1.postcode2)::text = (postcodes_1_1.postcode)::text)
22. 64.356 93.604 ↑ 1.1 142,718 1

Hash Join (cost=1,179.95..4,300.07 rows=156,204 width=12) (actual time=13.120..93.604 rows=142,718 loops=1)

  • Hash Cond: ((postcodes_zones_1_1.postcode)::text = (postcodes_point_dwh_1_1.postcode)::text)
23. 16.371 16.371 ↑ 1.0 156,204 1

Seq Scan on postcodes_zones_1 postcodes_zones_1_1 (cost=0.00..2,710.04 rows=156,204 width=11) (actual time=0.007..16.371 rows=156,204 loops=1)

24. 7.873 12.877 ↑ 1.0 38,309 1

Hash (cost=701.09..701.09 rows=38,309 width=16) (actual time=12.877..12.877 rows=38,309 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,309kB
25. 5.004 5.004 ↑ 1.0 38,309 1

Seq Scan on postcodes_point_dwh_1 postcodes_point_dwh_1_1 (cost=0.00..701.09 rows=38,309 width=16) (actual time=0.006..5.004 rows=38,309 loops=1)

26. 9.543 17.914 ↑ 1.0 40,591 1

Hash (cost=1,379.91..1,379.91 rows=40,591 width=18) (actual time=17.914..17.914 rows=40,591 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,495kB
27. 8.371 8.371 ↑ 1.0 40,591 1

Seq Scan on postcodes_1 postcodes_1_1 (cost=0.00..1,379.91 rows=40,591 width=18) (actual time=0.006..8.371 rows=40,591 loops=1)

28. 0.037 0.072 ↑ 1.0 160 1

Hash (cost=4.60..4.60 rows=160 width=10) (actual time=0.072..0.072 rows=160 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
29. 0.035 0.035 ↑ 1.0 160 1

Seq Scan on ward_1 ward_1_1 (cost=0.00..4.60 rows=160 width=10) (actual time=0.010..0.035 rows=160 loops=1)

30.          

CTE _percentile_quality_restaurants_count_metric_without_uncovered_

31. 0.444 244.392 ↑ 1.6 128 1

Sort (cost=8,019.61..8,020.11 rows=200 width=18) (actual time=244.383..244.392 rows=128 loops=1)

  • Sort Key: ward_1_2.wardcode
  • Sort Method: quicksort Memory: 35kB
32. 0.219 243.948 ↑ 1.6 128 1

HashAggregate (cost=8,009.96..8,011.96 rows=200 width=18) (actual time=243.922..243.948 rows=128 loops=1)

  • Group Key: ward_1_2.wardcode
33. 39.218 243.729 ↑ 12.9 633 1

HashAggregate (cost=7,805.96..7,887.56 rows=8,160 width=14) (actual time=243.521..243.729 rows=633 loops=1)

  • Group Key: ward_1_2.wardcode, postcodes_zones_1_2.restaurant_id
34. 40.845 204.511 ↑ 1.1 142,718 1

Hash Join (cost=3,073.85..7,024.94 rows=156,204 width=14) (actual time=30.470..204.511 rows=142,718 loops=1)

  • Hash Cond: ((postcodes_1_2.wardcode)::text = (ward_1_2.wardcode)::text)
35. 53.095 163.581 ↑ 1.1 142,718 1

Hash Join (cost=3,067.25..6,597.45 rows=156,204 width=14) (actual time=30.380..163.581 rows=142,718 loops=1)

  • Hash Cond: ((postcodes_point_dwh_1_2.postcode2)::text = (postcodes_1_2.postcode)::text)
36. 63.679 93.524 ↑ 1.1 142,718 1

Hash Join (cost=1,179.95..4,300.07 rows=156,204 width=12) (actual time=13.174..93.524 rows=142,718 loops=1)

  • Hash Cond: ((postcodes_zones_1_2.postcode)::text = (postcodes_point_dwh_1_2.postcode)::text)
37. 16.739 16.739 ↑ 1.0 156,204 1

Seq Scan on postcodes_zones_1 postcodes_zones_1_2 (cost=0.00..2,710.04 rows=156,204 width=11) (actual time=0.007..16.739 rows=156,204 loops=1)

38. 8.083 13.106 ↑ 1.0 38,309 1

Hash (cost=701.09..701.09 rows=38,309 width=16) (actual time=13.106..13.106 rows=38,309 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,309kB
39. 5.023 5.023 ↑ 1.0 38,309 1

Seq Scan on postcodes_point_dwh_1 postcodes_point_dwh_1_2 (cost=0.00..701.09 rows=38,309 width=16) (actual time=0.005..5.023 rows=38,309 loops=1)

40. 8.785 16.962 ↑ 1.0 40,591 1

Hash (cost=1,379.91..1,379.91 rows=40,591 width=18) (actual time=16.961..16.962 rows=40,591 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,495kB
41. 8.177 8.177 ↑ 1.0 40,591 1

Seq Scan on postcodes_1 postcodes_1_2 (cost=0.00..1,379.91 rows=40,591 width=18) (actual time=0.009..8.177 rows=40,591 loops=1)

42. 0.033 0.085 ↑ 1.0 160 1

Hash (cost=4.60..4.60 rows=160 width=10) (actual time=0.085..0.085 rows=160 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
43. 0.052 0.052 ↑ 1.0 160 1

Seq Scan on ward_1 ward_1_2 (cost=0.00..4.60 rows=160 width=10) (actual time=0.018..0.052 rows=160 loops=1)

44.          

CTE at_ward

45. 0.040 0.040 ↑ 1.0 160 1

Seq Scan on ward_1 ward_1_3 (cost=0.00..4.60 rows=160 width=79) (actual time=0.010..0.040 rows=160 loops=1)

46. 0.117 759.790 ↑ 1.6 128 1

WindowAgg (cost=65.34..69.34 rows=200 width=1,668) (actual time=759.691..759.790 rows=128 loops=1)

47. 0.068 759.673 ↑ 1.6 128 1

Sort (cost=65.34..65.84 rows=200 width=1,660) (actual time=759.666..759.673 rows=128 loops=1)

  • Sort Key: _percentile_quality_restaurants_count_metric_without_uncovered_._percentile_quality_restaurants_count_metric_without_uncovered_ NULLS FIRST
  • Sort Method: quicksort Memory: 53kB
48. 0.118 759.605 ↑ 1.6 128 1

WindowAgg (cost=54.19..57.69 rows=200 width=1,660) (actual time=759.488..759.605 rows=128 loops=1)

49. 0.064 759.487 ↑ 1.6 128 1

Sort (cost=54.19..54.69 rows=200 width=1,652) (actual time=759.480..759.487 rows=128 loops=1)

  • Sort Key: _rank_quality_restaurants_count_metric_without_uncovered_1._rank_quality_restaurants_count_metric_without_uncovered_1 DESC NULLS LAST
  • Sort Method: quicksort Memory: 46kB
50. 0.184 759.423 ↑ 1.6 128 1

HashAggregate (cost=44.55..46.55 rows=200 width=1,652) (actual time=759.393..759.423 rows=128 loops=1)

  • Group Key: quality_restaurants_count_metric_without_uncovered.quality_restaurants_count_metric_without_uncovered, COALESCE(quality_restaurants_count_metric_without_uncovered.wardcode, _rank_quality_restaurants_count_metric_without_uncovered_1.wardcode, _percentile_quality_restaurants_count_metric_without_uncovered_.wardcode),
51. 0.095 759.239 ↑ 1.6 128 1

Hash Left Join (cost=18.20..39.55 rows=200 width=1,652) (actual time=759.022..759.239 rows=128 loops=1)

  • Hash Cond: ((COALESCE(quality_restaurants_count_metric_without_uncovered.wardcode, _rank_quality_restaurants_count_metric_without_uncovered_1.wardcode, _percentile_quality_restaurants_count_metric_without_uncovered_.wardcode))::text = (at_ward.wardcode)::text)
52. 0.069 758.975 ↑ 1.6 128 1

Hash Full Join (cost=13.00..32.00 rows=200 width=1,572) (actual time=758.818..758.975 rows=128 loops=1)

  • Hash Cond: ((COALESCE(quality_restaurants_count_metric_without_uncovered.wardcode, _rank_quality_restaurants_count_metric_without_uncovered_1.wardcode))::text = (_percentile_quality_restaurants_count_metric_without_uncovered_.wardcode)::text)
53. 0.085 514.440 ↑ 1.6 128 1

Hash Full Join (cost=6.50..18.00 rows=200 width=1,048) (actual time=514.343..514.440 rows=128 loops=1)

  • Hash Cond: ((quality_restaurants_count_metric_without_uncovered.wardcode)::text = (_rank_quality_restaurants_count_metric_without_uncovered_1.wardcode)::text)
54. 268.617 268.617 ↑ 1.6 128 1

CTE Scan on quality_restaurants_count_metric_without_uncovered (cost=0.00..4.00 rows=200 width=524) (actual time=268.582..268.617 rows=128 loops=1)

55. 0.041 245.738 ↑ 1.6 128 1

Hash (cost=4.00..4.00 rows=200 width=524) (actual time=245.738..245.738 rows=128 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
56. 245.697 245.697 ↑ 1.6 128 1

CTE Scan on _rank_quality_restaurants_count_metric_without_uncovered_1 (cost=0.00..4.00 rows=200 width=524) (actual time=245.654..245.697 rows=128 loops=1)

57. 0.039 244.466 ↑ 1.6 128 1

Hash (cost=4.00..4.00 rows=200 width=524) (actual time=244.466..244.466 rows=128 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
58. 244.427 244.427 ↑ 1.6 128 1

CTE Scan on _percentile_quality_restaurants_count_metric_without_uncovered_ (cost=0.00..4.00 rows=200 width=524) (actual time=244.385..244.427 rows=128 loops=1)

59. 0.039 0.169 ↑ 1.0 160 1

Hash (cost=3.20..3.20 rows=160 width=1,628) (actual time=0.169..0.169 rows=160 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 27kB
60. 0.130 0.130 ↑ 1.0 160 1

CTE Scan on at_ward (cost=0.00..3.20 rows=160 width=1,628) (actual time=0.012..0.130 rows=160 loops=1)

Planning time : 2.505 ms
Execution time : 761.920 ms