explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KPpi

Settings
# exclusive inclusive rows x rows loops node
1. 65.926 3,247.705 ↑ 1.0 145,079 1

Nested Loop (cost=78,250.50..82,602.89 rows=145,079 width=40) (actual time=3,029.076..3,247.705 rows=145,079 loops=1)

2.          

CTE e0

3. 526.886 2,340.877 ↑ 1.0 145,079 1

Hash Left Join (cost=41,697.58..73,530.92 rows=145,079 width=324) (actual time=1,392.478..2,340.877 rows=145,079 loops=1)

  • Hash Cond: ((a.cbsd_id)::text = (b0.cbsd_id)::text)
4. 195.889 684.396 ↑ 1.0 145,079 1

Hash Right Join (cost=28,559.19..46,148.90 rows=145,079 width=292) (actual time=262.810..684.396 rows=145,079 loops=1)

  • Hash Cond: ((e0.cbsd_id)::text = (a.cbsd_id)::text)
5.          

CTE e0

6. 84.792 145.967 ↓ 1.0 110,217 1

GroupAggregate (cost=0.42..10,135.91 rows=110,140 width=38) (actual time=0.029..145.967 rows=110,217 loops=1)

  • Group Key: tb_cbsd_grants_current.cbsd_id
7. 61.175 61.175 ↓ 1.0 110,280 1

Index Scan using grants_current_cbsdid_idx on tb_cbsd_grants_current (cost=0.42..7,380.46 rows=110,270 width=39) (actual time=0.017..61.175 rows=110,280 loops=1)

  • Filter: active
  • Rows Removed by Filter: 12
8. 226.114 226.114 ↓ 1.0 110,217 1

CTE Scan on e0 (cost=0.00..2,202.80 rows=110,140 width=234) (actual time=0.032..226.114 rows=110,217 loops=1)

9. 135.460 262.393 ↑ 1.0 145,079 1

Hash (cost=11,650.79..11,650.79 rows=145,079 width=252) (actual time=262.392..262.393 rows=145,079 loops=1)

  • Buckets: 16384 Batches: 16 Memory Usage: 2755kB
10. 126.933 126.933 ↑ 1.0 145,079 1

Seq Scan on tb_cbsd_status_current a (cost=0.00..11,650.79 rows=145,079 width=252) (actual time=2.274..126.933 rows=145,079 loops=1)

  • Filter: active
11. 88.353 1,129.595 ↓ 1.0 110,217 1

Hash (cost=10,685.64..10,685.64 rows=110,140 width=54) (actual time=1,129.595..1,129.595 rows=110,217 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 8 (originally 4) Memory Usage: 3585kB
12. 51.588 1,041.242 ↓ 1.0 110,217 1

Subquery Scan on b0 (cost=0.42..10,685.64 rows=110,140 width=54) (actual time=0.072..1,041.242 rows=110,217 loops=1)

13. 918.433 989.654 ↓ 1.0 110,217 1

GroupAggregate (cost=0.42..9,584.24 rows=110,140 width=54) (actual time=0.070..989.654 rows=110,217 loops=1)

  • Group Key: tb_cbsd_grants_current_1.cbsd_id
14. 71.221 71.221 ↓ 1.0 110,280 1

Index Scan using grants_current_cbsdid_idx on tb_cbsd_grants_current tb_cbsd_grants_current_1 (cost=0.42..7,380.46 rows=110,270 width=82) (actual time=0.007..71.221 rows=110,280 loops=1)

  • Filter: active
  • Rows Removed by Filter: 12
15.          

CTE e1

16. 216.031 1,694.791 ↑ 1.0 145,079 1

WindowAgg (cost=0.00..4,715.07 rows=145,079 width=8) (actual time=1,626.649..1,694.791 rows=145,079 loops=1)

17. 1,478.760 1,478.760 ↑ 1.0 145,079 1

CTE Scan on e0 e0_1 (cost=0.00..2,901.58 rows=145,079 width=0) (actual time=0.001..1,478.760 rows=145,079 loops=1)

18.          

CTE e2

19. 0.049 1,402.170 ↑ 1.0 100 1

Limit (cost=0.00..2.25 rows=100 width=32) (actual time=1,392.519..1,402.170 rows=100 loops=1)

20. 1,402.121 1,402.121 ↑ 1,450.8 100 1

CTE Scan on e0 e0_2 (cost=0.00..3,264.28 rows=145,079 width=32) (actual time=1,392.518..1,402.121 rows=100 loops=1)

21.          

CTE e3

22. 0.123 1,402.390 ↑ 1.0 1 1

Aggregate (cost=2.25..2.26 rows=1 width=32) (actual time=1,402.390..1,402.390 rows=1 loops=1)

23. 1,402.267 1,402.267 ↑ 1.0 100 1

CTE Scan on e2 (cost=0.00..2.00 rows=100 width=32) (actual time=1,392.521..1,402.267 rows=100 loops=1)

24. 1,402.420 1,402.420 ↑ 1.0 1 1

CTE Scan on e3 (cost=0.00..0.02 rows=1 width=32) (actual time=1,402.419..1,402.420 rows=1 loops=1)

25. 1,779.359 1,779.359 ↑ 1.0 145,079 1

CTE Scan on e1 (cost=0.00..2,901.58 rows=145,079 width=8) (actual time=1,626.653..1,779.359 rows=145,079 loops=1)