explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZGRN

Settings
# exclusive inclusive rows x rows loops node
1. 2,331.250 39,526.401 ↑ 33.3 218 1

HashAggregate (cost=323,216.37..323,289.04 rows=7,267 width=524) (actual time=39,489.452..39,526.401 rows=218 loops=1)

  • Group Key: '2019-09-03 00:00:00'::timestamp without time zone, e1.owner_id, e1.cbsd_status, e1.state_name, e1.fcc_id, e1.cbsd_category
2.          

CTE e0

3. 0.000 0.021 ↓ 0.0 0 1

Unique (cost=49.24..49.84 rows=120 width=534) (actual time=0.021..0.021 rows=0 loops=1)

4. 0.010 0.021 ↓ 0.0 0 1

Sort (cost=49.24..49.54 rows=120 width=534) (actual time=0.020..0.021 rows=0 loops=1)

  • Sort Key: cbsd_status_p_2019_09.cbsd_id, cbsd_status_p_2019_09.msg_timestamp
  • Sort Method: quicksort Memory: 25kB
5. 0.002 0.011 ↓ 0.0 0 1

Append (cost=0.00..45.10 rows=120 width=534) (actual time=0.011..0.011 rows=0 loops=1)

6. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on cbsd_status_p_2019_09 (cost=0.00..11.12 rows=30 width=534) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (msg_timestamp >= '2019-09-03 00:40:21'::timestamp without time zone)
7. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on cbsd_status_p_2019_10 (cost=0.00..11.12 rows=30 width=534) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (msg_timestamp >= '2019-09-03 00:40:21'::timestamp without time zone)
8. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on cbscretatus_p_2019_11 (cost=0.00..11.12 rows=30 width=534) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (msg_timestamp >= '2019-09-03 00:40:21'::timestamp without time zone)
9. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on cbsd_status_p_2019_12 (cost=0.00..11.12 rows=30 width=534) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (msg_timestamp >= '2019-09-03 00:40:21'::timestamp without time zone)
10.          

CTE e1

11. 757.236 34,940.254 ↓ 2.0 145,079 1

Append (cost=0.15..320,441.44 rows=72,669 width=566) (actual time=11,288.426..34,940.254 rows=145,079 loops=1)

12. 0.001 0.024 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.15..278,968.31 rows=129 width=504) (actual time=0.024..0.024 rows=0 loops=1)

13. 0.001 0.023 ↓ 0.0 0 1

Nested Loop (cost=0.00..278,021.11 rows=120 width=533) (actual time=0.023..0.023 rows=0 loops=1)

14. 0.022 0.022 ↓ 0.0 0 1

CTE Scan on e0 a (cost=0.00..2.40 rows=120 width=526) (actual time=0.022..0.022 rows=0 loops=1)

15. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..2,316.80 rows=1 width=7) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Seq Scan on tb_cbsd_registration c0 (cost=0.00..55,603.26 rows=24 width=7) (never executed)

  • Filter: ((cbsd_id)::text = (a.cbsd_id)::text)
17. 0.000 0.000 ↓ 0.0 0

Index Scan using mycensus_geom_idx on tb_mycensus b (cost=0.15..7.88 rows=1 width=39,227) (never executed)

  • Index Cond: (geom ~ a.location)
  • Filter: _st_contains(geom, a.location)
18. 1,970.824 34,182.994 ↓ 2.0 145,079 1

Subquery Scan on *SELECT* 2 (cost=24,878.89..41,108.49 rows=72,540 width=73) (actual time=11,288.401..34,182.994 rows=145,079 loops=1)

19. 1,238.290 32,212.170 ↓ 2.0 145,079 1

Subquery Scan on vw_cbsd_status_active (cost=24,878.89..40,383.09 rows=72,540 width=73) (actual time=11,288.400..32,212.170 rows=145,079 loops=1)

  • Filter: (NOT (hashed SubPlan 2))
20. 3,933.939 30,973.879 ↑ 1.0 145,079 1

Hash Right Join (cost=24,876.19..38,566.90 rows=145,079 width=1,125) (actual time=11,288.381..30,973.879 rows=145,079 loops=1)

  • Hash Cond: ((e0_1.cbsd_id)::text = (a_1.cbsd_id)::text)
21.          

CTE e0

22. 1,752.970 13,283.733 ↓ 1.0 110,217 1

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

  • Group Key: tb_cbsd_grants_current.cbsd_id
23. 11,530.763 11,530.763 ↓ 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=18.451..11,530.763 rows=110,280 loops=1)

  • Filter: active
  • Rows Removed by Filter: 12
24. 15,770.690 15,770.690 ↓ 1.0 110,217 1

CTE Scan on e0 e0_1 (cost=0.00..2,202.80 rows=110,140 width=226) (actual time=18.471..15,770.690 rows=110,217 loops=1)

25. 2,096.382 11,269.250 ↑ 1.0 145,079 1

Hash (cost=11,650.79..11,650.79 rows=145,079 width=41) (actual time=11,269.250..11,269.250 rows=145,079 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 3129kB
26. 9,172.868 9,172.868 ↑ 1.0 145,079 1

Seq Scan on tb_cbsd_status_current a_1 (cost=0.00..11,650.79 rows=145,079 width=41) (actual time=5,029.850..9,172.868 rows=145,079 loops=1)

  • Filter: active
27.          

SubPlan (forSubquery Scan)

28. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on e0 (cost=0.00..2.40 rows=120 width=218) (actual time=0.000..0.001 rows=0 loops=1)

29. 37,195.151 37,195.151 ↓ 2.0 145,079 1

CTE Scan on e1 (cost=0.00..1,453.38 rows=72,669 width=356) (actual time=11,288.430..37,195.151 rows=145,079 loops=1)