explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q2gA

Settings

Optimization(s) for this plan:

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

Limit (cost=24,892.79..24,895.29 rows=100 width=692) (actual time=783.312..783.346 rows=100 loops=1)

  • loops=1)
  • loops=1)
  • loops=1)
2. 0.093 783.334 ↑ 8.2 100 1

WindowAgg (cost=24,892.79..24,913.19 rows=816 width=692) (actual time=783.310..783.334 rows=100 loops=1)

3. 0.036 783.241 ↑ 5.1 160 1

Subquery Scan on inner (cost=24,892.79..24,902.99 rows=816 width=684) (actual time=783.196..783.241 rows=160 loops=1)

4. 0.104 783.205 ↑ 5.1 160 1

Sort (cost=24,892.79..24,894.83 rows=816 width=700) (actual time=783.194..783.205 rows=160 loops=1)

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

CTE wardname

6. 0.468 0.637 ↑ 1.0 160 1

Sort (cost=13.26..13.66 rows=160 width=106) (actual time=0.626..0.637 rows=160 loops=1)

  • Sort Key: ward_1.wardcode, ward_1.x_min, ward_1.x_max, ward_1.y_min, ward_1.y_max
  • Sort Method: quicksort Memory: 47kB
7. 0.141 0.169 ↑ 1.0 160 1

HashAggregate (cost=5.80..7.40 rows=160 width=106) (actual time=0.119..0.169 rows=160 loops=1)

  • Group Key: ward_1.wardcode
8. 0.028 0.028 ↑ 1.0 160 1

Seq Scan on ward_1 (cost=0.00..4.60 rows=160 width=79) (actual time=0.011..0.028 rows=160 loops=1)

9.          

CTE quality_restaurants_count_metric_without_uncovered

10. 0.452 253.406 ↑ 6.4 128 1

Sort (cost=8,139.19..8,141.23 rows=816 width=50) (actual time=253.398..253.406 rows=128 loops=1)

  • Sort Key: ward_1_1.wardcode, ward_1_1.x_min, ward_1_1.x_max, ward_1_1.y_min, ward_1_1.y_max
  • Sort Method: quicksort Memory: 43kB
11. 0.586 252.954 ↑ 6.4 128 1

HashAggregate (cost=8,091.56..8,099.72 rows=816 width=50) (actual time=252.919..252.954 rows=128 loops=1)

  • Group Key: ward_1_1.wardcode, ward_1_1.x_min, ward_1_1.x_max, ward_1_1.y_min, ward_1_1.y_max
12. 88.538 252.368 ↑ 12.9 633 1

HashAggregate (cost=7,805.96..7,887.56 rows=8,160 width=46) (actual time=252.167..252.368 rows=633 loops=1)

  • Group Key: ward_1_1.wardcode, postcodes_zones_1.restaurant_id
  • -> Hash Join (cost=3,073.85..7024.94 rows=156,204 width=46) (actual time=34.376..211.570 rows=142,718
  • Hash Cond: ((postcodes_1.wardcode)::text = (ward_1_1.wardcode)::text)
13. 51.687 163.749 ↑ 1.1 142,718 1

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

  • Hash Cond: ((postcodes_point_dwh_1.postcode2)::text = (postcodes_1.postcode)::text)
14. 62.426 92.782 ↑ 1.1 142,718 1

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

  • Hash Cond: ((postcodes_zones_1.postcode)::text = (postcodes_point_dwh_1.postcode)::text)
15. 15.877 15.877 ↑ 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.004..15.877 rows=156,204 loops=1)

16. 8.520 14.479 ↑ 1.0 38,309 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,309kB
17. 5.959 5.959 ↑ 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.005..5.959 rows=38,309 loops=1)

18. 10.042 19.280 ↑ 1.0 40,591 1

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

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

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

20. 0.050 0.081 ↑ 1.0 160 1

Hash (cost=4.60..4.60 rows=160 width=42) (actual time=0.081..0.081 rows=160 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
21. 0.031 0.031 ↑ 1.0 160 1

Seq Scan on ward_1 ward_1_1 (cost=0.00..4.60 rows=160 width=42) (actual time=0.003..0.031 rows=160 loops=1)

22.          

CTE _rank_quality_restaurants_count_metric_without_uncovered_1

23. 0.379 255.664 ↑ 6.4 128 1

Sort (cost=8,139.19..8,141.23 rows=816 width=50) (actual time=255.656..255.664 rows=128 loops=1)

  • Sort Key: ward_1_2.wardcode, ward_1_2.x_min, ward_1_2.x_max, ward_1_2.y_min, ward_1_2.y_max
  • Sort Method: quicksort Memory: 43kB
24. 0.597 255.285 ↑ 6.4 128 1

HashAggregate (cost=8,091.56..8,099.72 rows=816 width=50) (actual time=255.257..255.285 rows=128 loops=1)

  • Group Key: ward_1_2.wardcode, ward_1_2.x_min, ward_1_2.x_max, ward_1_2.y_min, ward_1_2.y_max
25. 88.771 254.688 ↑ 12.9 633 1

HashAggregate (cost=7,805.96..7,887.56 rows=8,160 width=46) (actual time=254.472..254.688 rows=633 loops=1)

  • Group Key: ward_1_2.wardcode, postcodes_zones_1_1.restaurant_id
  • -> Hash Join (cost=3,073.85..7024.94 rows=156,204 width=46) (actual time=35.041..213.786 rows=142,718
  • Hash Cond: ((postcodes_1_1.wardcode)::text = (ward_1_2.wardcode)::text)
26. 52.018 165.816 ↑ 1.1 142,718 1

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

  • Hash Cond: ((postcodes_point_dwh_1_1.postcode2)::text = (postcodes_1_1.postcode)::text)
27. 63.403 94.281 ↑ 1.1 142,718 1

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

  • Hash Cond: ((postcodes_zones_1_1.postcode)::text = (postcodes_point_dwh_1_1.postcode)::text)
28. 16.028 16.028 ↑ 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.028 rows=156,204 loops=1)

29. 8.782 14.850 ↑ 1.0 38,309 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,309kB
30. 6.068 6.068 ↑ 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.009..6.068 rows=38,309 loops=1)

31. 10.042 19.517 ↑ 1.0 40,591 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,495kB
32. 9.475 9.475 ↑ 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.007..9.475 rows=40,591 loops=1)

33. 0.049 0.101 ↑ 1.0 160 1

Hash (cost=4.60..4.60 rows=160 width=42) (actual time=0.101..0.101 rows=160 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
34. 0.052 0.052 ↑ 1.0 160 1

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

35.          

CTE _percentile_quality_restaurants_count_metric_without_uncovered_

36. 0.475 270.422 ↑ 6.4 128 1

Sort (cost=8,139.19..8,141.23 rows=816 width=50) (actual time=270.413..270.422 rows=128 loops=1)

  • Sort Key: ward_1_3.wardcode, ward_1_3.x_min, ward_1_3.x_max, ward_1_3.y_min, ward_1_3.y_max
  • Sort Method: quicksort Memory: 43kB
37. 0.641 269.947 ↑ 6.4 128 1

HashAggregate (cost=8,091.56..8,099.72 rows=816 width=50) (actual time=269.907..269.947 rows=128 loops=1)

  • Group Key: ward_1_3.wardcode, ward_1_3.x_min, ward_1_3.x_max, ward_1_3.y_min, ward_1_3.y_max
38. 92.121 269.306 ↑ 12.9 633 1

HashAggregate (cost=7,805.96..7,887.56 rows=8,160 width=46) (actual time=269.051..269.306 rows=633 loops=1)

  • Group Key: ward_1_3.wardcode, postcodes_zones_1_2.restaurant_id
  • -> Hash Join (cost=3,073.85..7024.94 rows=156,204 width=46) (actual time=36.895..227.220 rows=142,718
  • Hash Cond: ((postcodes_1_2.wardcode)::text = (ward_1_3.wardcode)::text)
39. 55.671 177.100 ↑ 1.1 142,718 1

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

  • Hash Cond: ((postcodes_point_dwh_1_2.postcode2)::text = (postcodes_1_2.postcode)::text)
40. 67.535 99.082 ↑ 1.1 142,718 1

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

  • Hash Cond: ((postcodes_zones_1_2.postcode)::text = (postcodes_point_dwh_1_2.postcode)::text)
41. 17.617 17.617 ↑ 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.006..17.617 rows=156,204 loops=1)

42. 8.254 13.930 ↑ 1.0 38,309 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,309kB
43. 5.676 5.676 ↑ 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.676 rows=38,309 loops=1)

44. 10.657 22.347 ↑ 1.0 40,591 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,495kB
45. 11.690 11.690 ↑ 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.011..11.690 rows=40,591 loops=1)

46. 0.047 0.085 ↑ 1.0 160 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
47. 0.038 0.038 ↑ 1.0 160 1

Seq Scan on ward_1 ward_1_3 (cost=0.00..4.60 rows=160 width=42) (actual time=0.009..0.038 rows=160 loops=1)

48. 0.160 783.101 ↑ 5.1 160 1

WindowAgg (cost=399.67..415.99 rows=816 width=700) (actual time=782.976..783.101 rows=160 loops=1)

49. 782.941 782.941 ↑ 5.1 160 1

Sort (cost=399.67..401.71 rows=816 width=692) (actual time=782.932..782.941 rows=160 loops=1)

  • Sort Key: _percentile_quality_restaurants_count_metric_without_uncovered_._percentile_quality_restaurants_count_metric_without_uncovered_ NULLS FIRST
  • Sort Method: quicksort Memory: 58kB