explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kBsb : Optimization for: US; plan #G1e3

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,104.979 1,104.979 ↓ 8,680.0 8,680 1

CTE Scan on cte_base (cost=94,975.40..94,975.42 rows=1 width=956) (actual time=693.944..1,104.979 rows=8,680 loops=1)

2.          

CTE cte_topnasn

3. 0.052 0.052 ↑ 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.052 rows=15 loops=1)

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

CTE cte_base

5. 174.562 1,100.912 ↓ 8,680.0 8,680 1

Nested Loop (cost=93,567.32..94,799.86 rows=1 width=501) (actual time=693.941..1,100.912 rows=8,680 loops=1)

  • Join Filter: ((perf.asn)::text = t.asn_id)
  • Rows Removed by Join Filter: 2175410
6. 0.000 780.744 ↓ 145,606.0 145,606 1

Gather Merge (cost=93,567.32..94,796.51 rows=1 width=93) (actual time=656.046..780.744 rows=145,606 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 147.945 2,098.350 ↓ 48,535.0 48,535 3

Merge Join (cost=92,567.29..93,796.37 rows=1 width=93) (actual time=618.490..699.450 rows=48,535 loops=3)

  • Merge Cond: (((perf.asn)::text = th.asn) AND ((perf.cdn)::text = (th.cdn)::text) AND ((perf.state)::text = th.state))
8. 521.733 1,208.379 ↑ 1.2 48,854 3

Sort (cost=81,880.05..82,031.77 rows=60,691 width=75) (actual time=386.919..402.793 rows=48,854 loops=3)

  • Sort Key: perf.asn, perf.cdn, perf.state
  • Sort Method: external merge Disk: 5056kB
9. 10.584 686.646 ↑ 1.2 48,854 3

Append (cost=0.00..74,359.89 rows=60,691 width=75) (actual time=207.481..228.882 rows=48,854 loops=3)

10. 162.453 162.453 ↓ 0.0 0 3

Parallel Seq Scan on cdn_isp_performance_us_2019_10_31_20 perf (cost=0.00..18,580.78 rows=1 width=75) (actual time=54.151..54.151 rows=0 loops=3)

  • Filter: (((country)::text = 'US'::text) AND (processedtimestamp <= timezone('utc'::text, CURRENT_TIMESTAMP)) AND (processedtimestamp >= (timezone('utc'::text, CURRENT_TIMESTAMP) - '00:10:00'::interval)))
  • Rows Removed by Filter: 231032
11. 207.204 207.204 ↓ 0.0 0 3

Parallel Seq Scan on cdn_isp_performance_us_2019_11_8_16 perf_1 (cost=0.00..23,236.61 rows=1 width=75) (actual time=69.068..69.068 rows=0 loops=3)

  • Filter: (((country)::text = 'US'::text) AND (processedtimestamp <= timezone('utc'::text, CURRENT_TIMESTAMP)) AND (processedtimestamp >= (timezone('utc'::text, CURRENT_TIMESTAMP) - '00:10:00'::interval)))
  • Rows Removed by Filter: 288070
12. 221.172 221.172 ↓ 0.0 0 3

Parallel Seq Scan on cdn_isp_performance_us_2019_11_8_17 perf_2 (cost=0.00..24,763.20 rows=1 width=75) (actual time=73.724..73.724 rows=0 loops=3)

  • Filter: (((country)::text = 'US'::text) AND (processedtimestamp <= timezone('utc'::text, CURRENT_TIMESTAMP)) AND (processedtimestamp >= (timezone('utc'::text, CURRENT_TIMESTAMP) - '00:10:00'::interval)))
  • Rows Removed by Filter: 307002
13. 85.230 85.230 ↑ 1.2 48,854 3

Parallel Seq Scan on cdn_isp_performance_us_2019_11_8_18 perf_3 (cost=0.00..7,741.36 rows=60,685 width=75) (actual time=10.536..28.410 rows=48,854 loops=3)

  • Filter: (((country)::text = 'US'::text) AND (processedtimestamp <= timezone('utc'::text, CURRENT_TIMESTAMP)) AND (processedtimestamp >= (timezone('utc'::text, CURRENT_TIMESTAMP) - '00:10:00'::interval)))
  • Rows Removed by Filter: 47103
14. 0.003 0.003 ↓ 0.0 0 3

Parallel Seq Scan on cdn_isp_performance_us_2019_11_8_19 perf_4 (cost=0.00..12.65 rows=1 width=506) (actual time=0.001..0.001 rows=0 loops=3)

  • Filter: (((country)::text = 'US'::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 3

Parallel Seq Scan on cdn_isp_performance_us_2019_11_8_20 perf_5 (cost=0.00..12.65 rows=1 width=506) (actual time=0.000..0.000 rows=0 loops=3)

  • Filter: (((country)::text = 'US'::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 3

Parallel Seq Scan on cdn_isp_performance_us_2019_11_8_21 perf_6 (cost=0.00..12.65 rows=1 width=506) (actual time=0.000..0.000 rows=0 loops=3)

  • Filter: (((country)::text = 'US'::text) AND (processedtimestamp <= timezone('utc'::text, CURRENT_TIMESTAMP)) AND (processedtimestamp >= (timezone('utc'::text, CURRENT_TIMESTAMP) - '00:10:00'::interval)))
17. 702.321 742.026 ↓ 1.5 95,216 3

Sort (cost=10,687.24..10,842.78 rows=62,216 width=30) (actual time=231.559..247.342 rows=95,216 loops=3)

  • Sort Key: th.asn, th.cdn, th.state
  • Sort Method: external sort Disk: 2512kB
18. 30.738 39.705 ↓ 1.0 62,673 3

Bitmap Heap Scan on cdn_threshold th (cost=1,878.60..5,733.30 rows=62,216 width=30) (actual time=3.116..13.235 rows=62,673 loops=3)

  • Recheck Cond: (country = 'US'::text)
  • Heap Blocks: exact=1194
19. 8.967 8.967 ↓ 1.0 62,673 3

Bitmap Index Scan on ix_cdn_threshold (cost=0.00..1,863.04 rows=62,216 width=0) (actual time=2.989..2.989 rows=62,673 loops=3)

  • Index Cond: (country = 'US'::text)
20. 145.606 145.606 ↑ 6.9 15 145,606

CTE Scan on cte_topnasn t (cost=0.00..2.06 rows=103 width=450) (actual time=0.000..0.001 rows=15 loops=145,606)

Planning time : 142.760 ms
Execution time : 1,107.952 ms