explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xk9u : test

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 447.040 ↓ 0.0 0 1

GroupAggregate (cost=8,195.21..8,195.25 rows=1 width=274) (actual time=447.040..447.040 rows=0 loops=1)

  • Group Key: cte_base.processedtime, cte_base.key, (CURRENT_TIMESTAMP), ((CURRENT_TIMESTAMP - '00:10:00'::interval))
2.          

CTE cte_topnasn

3. 0.044 0.044 ↑ 6.9 15 1

Index Scan using idx_country_rank on country_asn_rank r (cost=0.41..175.54 rows=103 width=31) (actual time=0.018..0.044 rows=15 loops=1)

  • Index Cond: ((country = 'US'::text) AND (asn_rank <= 15))
4.          

CTE cte_base

5. 0.031 447.011 ↓ 0.0 0 1

Nested Loop (cost=1,000.42..8,019.63 rows=1 width=514) (actual time=447.011..447.011 rows=0 loops=1)

6. 0.000 446.746 ↓ 117.0 117 1

Nested Loop (cost=1,000.42..8,017.30 rows=1 width=96) (actual time=23.322..446.746 rows=117 loops=1)

  • Join Filter: (((perf.cdn)::text = (th.cdn)::text) AND ((perf.state)::text = th.state))
  • Rows Removed by Join Filter: 2691
7. 0.521 0.521 ↓ 24.0 24 1

Index Scan using ix_cdn_threshold on cdn_threshold th (cost=0.42..142.64 rows=1 width=30) (actual time=0.072..0.521 rows=24 loops=1)

  • Index Cond: ((country = 'GB'::text) AND (asn = '5089'::text))
8. 204.312 448.560 ↑ 1.1 117 24

Gather (cost=1,000.00..7,872.79 rows=125 width=87) (actual time=16.878..18.690 rows=117 loops=24)

  • Workers Planned: 1
  • Workers Launched: 1
9. 0.192 244.248 ↑ 1.3 58 48

Append (cost=0.00..6,860.29 rows=76 width=87) (actual time=9.710..10.177 rows=58 loops=48)

10. 79.440 79.440 ↓ 0.0 0 48

Parallel Seq Scan on cdn_isp_performance_gb_2019_10_31_20 perf (cost=0.00..2,273.69 rows=1 width=77) (actual time=3.310..3.310 rows=0 loops=48)

  • Filter: (((asn)::text = '5089'::text) AND ((country)::text = 'GB'::text) AND (processedtimestamp <= timezone('utc'::text, CURRENT_TIMESTAMP)) AND (processedtimestamp >= (timezone('utc'::text, CURRENT_TIMESTAMP) - '00:10:00'::interval)))
  • Rows Removed by Filter: 33246
11. 72.336 72.336 ↓ 0.0 0 48

Parallel Seq Scan on cdn_isp_performance_gb_2019_11_8_16 perf_1 (cost=0.00..2,084.20 rows=1 width=77) (actual time=3.014..3.014 rows=0 loops=48)

  • Filter: (((asn)::text = '5089'::text) AND ((country)::text = 'GB'::text) AND (processedtimestamp <= timezone('utc'::text, CURRENT_TIMESTAMP)) AND (processedtimestamp >= (timezone('utc'::text, CURRENT_TIMESTAMP) - '00:10:00'::interval)))
  • Rows Removed by Filter: 30286
12. 74.400 74.400 ↓ 0.0 0 48

Parallel Seq Scan on cdn_isp_performance_gb_2019_11_8_17 perf_2 (cost=0.00..2,024.01 rows=1 width=77) (actual time=3.100..3.100 rows=0 loops=48)

  • Filter: (((asn)::text = '5089'::text) AND ((country)::text = 'GB'::text) AND (processedtimestamp <= timezone('utc'::text, CURRENT_TIMESTAMP)) AND (processedtimestamp >= (timezone('utc'::text, CURRENT_TIMESTAMP) - '00:10:00'::interval)))
  • Rows Removed by Filter: 29099
13. 17.880 17.880 ↑ 1.2 58 48

Parallel Seq Scan on cdn_isp_performance_gb_2019_11_8_18 perf_3 (cost=0.00..439.79 rows=70 width=77) (actual time=0.283..0.745 rows=58 loops=48)

  • Filter: (((asn)::text = '5089'::text) AND ((country)::text = 'GB'::text) AND (processedtimestamp <= timezone('utc'::text, CURRENT_TIMESTAMP)) AND (processedtimestamp >= (timezone('utc'::text, CURRENT_TIMESTAMP) - '00:10:00'::interval)))
  • Rows Removed by Filter: 6318
14. 0.000 0.000 ↓ 0.0 0 48

Parallel Seq Scan on cdn_isp_performance_gb_2019_11_8_19 perf_4 (cost=0.00..12.87 rows=1 width=506) (actual time=0.000..0.000 rows=0 loops=48)

  • Filter: (((asn)::text = '5089'::text) AND ((country)::text = 'GB'::text) AND (processedtimestamp <= timezone('utc'::text, CURRENT_TIMESTAMP)) AND (processedtimestamp >= (timezone('utc'::text, CURRENT_TIMESTAMP) - '00:10:00'::interval)))
15. 0.000 0.000 ↓ 0.0 0 48

Parallel Seq Scan on cdn_isp_performance_gb_2019_11_8_20 perf_5 (cost=0.00..12.87 rows=1 width=506) (actual time=0.000..0.000 rows=0 loops=48)

  • Filter: (((asn)::text = '5089'::text) AND ((country)::text = 'GB'::text) AND (processedtimestamp <= timezone('utc'::text, CURRENT_TIMESTAMP)) AND (processedtimestamp >= (timezone('utc'::text, CURRENT_TIMESTAMP) - '00:10:00'::interval)))
16. 0.000 0.000 ↓ 0.0 0 48

Parallel Seq Scan on cdn_isp_performance_gb_2019_11_8_21 perf_6 (cost=0.00..12.87 rows=1 width=506) (actual time=0.000..0.000 rows=0 loops=48)

  • Filter: (((asn)::text = '5089'::text) AND ((country)::text = 'GB'::text) AND (processedtimestamp <= timezone('utc'::text, CURRENT_TIMESTAMP)) AND (processedtimestamp >= (timezone('utc'::text, CURRENT_TIMESTAMP) - '00:10:00'::interval)))
17. 0.234 0.234 ↓ 0.0 0 117

CTE Scan on cte_topnasn t (cost=0.00..2.32 rows=1 width=450) (actual time=0.002..0.002 rows=0 loops=117)

  • Filter: (asn_id = '5089'::text)
  • Rows Removed by Filter: 15
18. 0.027 447.039 ↓ 0.0 0 1

Sort (cost=0.04..0.05 rows=1 width=274) (actual time=447.039..447.039 rows=0 loops=1)

  • Sort Key: cte_base.processedtime, cte_base.key
  • Sort Method: quicksort Memory: 25kB
19. 447.012 447.012 ↓ 0.0 0 1

CTE Scan on cte_base (cost=0.00..0.03 rows=1 width=274) (actual time=447.012..447.012 rows=0 loops=1)

  • Filter: ((state)::text = 'ENG'::text)
Planning time : 240.878 ms
Execution time : 450.015 ms