explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tYAP : Optimization for: plan #vkd1

Settings

Optimization path:

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

Limit (cost=10,446.01..10,448.51 rows=100 width=716) (actual time=208.662..208.696 rows=100 loops=1)

  • rows=156,204 loops=1)
  • (actual time=0.007..21.323 rows=156,204 loops=1)
  • loops=1)
  • loops=1)
2. 0.072 208.684 ↑ 1.6 100 1

WindowAgg (cost=10,446.01..10,450.01 rows=160 width=716) (actual time=208.661..208.684 rows=100 loops=1)

3. 0.026 208.612 ↑ 1.3 119 1

Subquery Scan on inner (cost=10,446.01..10,448.01 rows=160 width=708) (actual time=208.580..208.612 rows=119 loops=1)

4. 0.079 208.586 ↑ 1.3 119 1

Sort (cost=10,446.01..10,446.41 rows=160 width=772) (actual time=208.578..208.586 rows=119 loops=1)

  • Sort Key: wardname.orders_turnover_metric DESC NULLS LAST
  • Sort Method: quicksort Memory: 56kB
5.          

CTE wardname

6. 0.318 207.896 ↑ 1.3 119 1

Sort (cost=10,406.84..10,407.24 rows=160 width=170) (actual time=207.888..207.896 rows=119 loops=1)

  • Sort Key: ward_1.wardcode
  • Sort Method: quicksort Memory: 41kB
7. 24.249 207.578 ↑ 1.3 119 1

HashAggregate (cost=10,398.18..10,400.98 rows=160 width=170) (actual time=207.525..207.578 rows=119 loops=1)

  • Group Key: ward_1.wardcode
8. 13.901 183.329 ↑ 1.0 40,880 1

Hash Join (cost=7,736.44..9,782.58 rows=41,040 width=53) (actual time=95.876..183.329 rows=40,880 loops=1)

  • Hash Cond: ((postcodes_1.wardcode)::text = (ward_1.wardcode)::text)
9. 18.761 169.329 ↑ 1.0 40,880 1

Hash Join (cost=7,729.84..9,665.40 rows=41,040 width=16) (actual time=95.772..169.329 rows=40,880 loops=1)

  • Hash Cond: ((postcodes_point_dwh_1.postcode2)::text = (postcodes_1.postcode)::text)
10. 18.354 129.867 ↑ 1.0 40,880 1

Hash Join (cost=5,842.54..7,670.36 rows=41,040 width=14) (actual time=75.012..129.867 rows=40,880 loops=1)

  • Hash Cond: ((postcodes_zones_1.postcode)::text = (postcodes_point_dwh_1.postcode)::text)
11. 86.028 98.089 ↑ 1.0 41,006 1

Hash Join (cost=4,662.59..6,382.67 rows=41,040 width=13) (actual time=61.521..98.089 rows=41,006 loops=1)

  • Hash Cond: ((orders_dwh_1.postcode_zone_id)::text = (postcodes_zones_1.postcode_zone_id)::text)
  • -> Hash (cost=2,710.04..2710.04 rows=156,204 width=17) (actual time=61.278..61.278
12. 12.061 12.061 ↑ 1.0 41,006 1

Seq Scan on orders_dwh_1 (cost=0.00..1,612.35 rows=41,040 width=16) (actual time=0.014..12.061 rows=41,006 loops=1)

  • Filter: ((order_time >= '2020-06-02'::date) AND (order_time <= '2020-06-29'::date))
  • Rows Removed by Filter: 9,284
  • Buckets: 262,144 Batches: 1 Memory Usage: 9,791kB
13. 0.000 0.000 ↓ 0.0

Seq Scan on postcodes_zones_1 (cost=0.00..2,710.04 rows=156,204 width=17) (actual rows= loops=)

14. 7.852 13.424 ↑ 1.0 38,309 1

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

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

16. 10.148 20.701 ↑ 1.0 40,591 1

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

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

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

18. 0.099 0.099 ↑ 1.0 160 1

Hash (cost=4.60..4.60 rows=160 width=47) (actual time=0.098..0.099 rows=160 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
  • -> Seq Scan on ward_1 (cost=0.00..4.60 rows=160 width=47) (actual time=0.009..0.048 rows=160
19.          

CTE at_ward

20. 0.039 0.039 ↑ 1.0 160 1

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

21. 0.122 208.507 ↑ 1.3 119 1

WindowAgg (cost=25.12..28.32 rows=160 width=772) (actual time=208.401..208.507 rows=119 loops=1)

22. 0.071 208.385 ↑ 1.3 119 1

Sort (cost=25.12..25.52 rows=160 width=764) (actual time=208.378..208.385 rows=119 loops=1)

  • Sort Key: wardname._percentile_orders_turnover_metric_1 NULLS FIRST
  • Sort Method: quicksort Memory: 49kB
23. 0.115 208.314 ↑ 1.3 119 1

WindowAgg (cost=16.46..19.26 rows=160 width=764) (actual time=208.197..208.314 rows=119 loops=1)

24. 0.138 208.199 ↑ 1.3 119 1

Sort (cost=16.46..16.86 rows=160 width=756) (actual time=208.191..208.199 rows=119 loops=1)

  • Sort Key: wardname._rank_orders_turnover_metric_1 DESC NULLS LAST
  • Sort Method: quicksort Memory: 44kB
  • -> Hash Left Join (cost=5.20..10.60 rows=160 width=756) (actual time=208.041..208.127 rows=119
  • Hash Cond: ((COALESCE(wardname.wardcode))::text = (at_ward.wardcode)::text)
25. 207.922 207.922 ↑ 1.3 119 1

CTE Scan on wardname (cost=0.00..3.20 rows=160 width=676) (actual time=207.890..207.922 rows=119 loops=1)

26. 0.029 0.139 ↑ 1.0 160 1

Hash (cost=3.20..3.20 rows=160 width=596) (actual time=0.139..0.139 rows=160 loops=1)

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

CTE Scan on at_ward (cost=0.00..3.20 rows=160 width=596) (actual time=0.011..0.110 rows=160 loops=1)

Planning time : 1.505 ms
Execution time : 209.218 ms