explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6Cu1

Settings
# exclusive inclusive rows x rows loops node
1. 402.416 108,375.952 ↑ 50.9 143 1

HashAggregate (cost=148,112.61..148,185.37 rows=7,276 width=532) (actual time=108,375.801..108,375.952 rows=143 loops=1)

  • Group Key: '2019-08-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. 569.048 8,997.434 ↓ 725.4 145,079 1

Unique (cost=99,757.43..101,934.22 rows=200 width=81) (actual time=5,569.178..8,997.434 rows=145,079 loops=1)

4. 6,644.928 8,428.386 ↑ 1.0 435,237 1

Sort (cost=99,757.43..100,845.83 rows=435,357 width=81) (actual time=5,569.175..8,428.386 rows=435,237 loops=1)

  • Sort Key: cbsd_status_p_2019_08.cbsd_id, cbsd_status_p_2019_08.msg_timestamp
  • Sort Method: external merge Disk: 38368kB
5. 163.855 1,783.458 ↑ 1.0 435,237 1

Append (cost=0.00..38,146.75 rows=435,357 width=81) (actual time=1.004..1,783.458 rows=435,237 loops=1)

6. 1,619.589 1,619.589 ↑ 1.0 435,237 1

Seq Scan on cbsd_status_p_2019_08 (cost=0.00..35,925.46 rows=435,237 width=81) (actual time=1.003..1,619.589 rows=435,237 loops=1)

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

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

  • Filter: (msg_timestamp >= '2019-08-03 00:40:21'::timestamp without time zone)
8. 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-08-03 00:40:21'::timestamp without time zone)
9. 0.003 0.003 ↓ 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.003 rows=0 loops=1)

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

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

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

CTE e1

12. 140.344 107,008.409 ↓ 1.7 124,005 1

Append (cost=0.57..43,450.04 rows=72,756 width=566) (actual time=5,594.376..107,008.409 rows=124,005 loops=1)

13. 484.803 105,713.325 ↓ 574.1 124,005 1

Nested Loop Left Join (cost=0.57..1,973.81 rows=216 width=504) (actual time=5,594.375..105,713.325 rows=124,005 loops=1)

14. 496.581 31,073.532 ↓ 620.0 124,005 1

Nested Loop (cost=0.42..929.81 rows=200 width=533) (actual time=5,576.481..31,073.532 rows=124,005 loops=1)

15. 9,395.417 9,395.417 ↓ 725.4 145,079 1

CTE Scan on e0 a (cost=0.00..4.00 rows=200 width=526) (actual time=5,569.183..9,395.417 rows=145,079 loops=1)

16. 580.316 21,181.534 ↑ 1.0 1 145,079

Limit (cost=0.42..4.61 rows=1 width=7) (actual time=0.145..0.146 rows=1 loops=145,079)

17. 20,601.218 20,601.218 ↑ 24.0 1 145,079

Index Scan using tb_cbsd_registration_cbsd_id_idx on tb_cbsd_registration c0 (cost=0.42..100.84 rows=24 width=7) (actual time=0.142..0.142 rows=1 loops=145,079)

  • Index Cond: ((cbsd_id)::text = (a.cbsd_id)::text)
18. 74,154.990 74,154.990 ↑ 1.0 1 124,005

Index Scan using mycensus_geom_idx on tb_mycensus b (cost=0.15..5.21 rows=1 width=39,227) (actual time=0.456..0.598 rows=1 loops=124,005)

  • Index Cond: (geom ~ a.location)
  • Filter: _st_contains(geom, a.location)
  • Rows Removed by Filter: 1
19. 0.328 1,154.740 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=24,880.69..41,110.29 rows=72,540 width=73) (actual time=1,154.740..1,154.740 rows=0 loops=1)

20. 163.358 1,154.412 ↓ 0.0 0 1

Subquery Scan on vw_cbsd_status_active (cost=24,880.69..40,384.89 rows=72,540 width=73) (actual time=1,154.412..1,154.412 rows=0 loops=1)

  • Filter: (NOT (hashed SubPlan 2))
  • Rows Removed by Filter: 145079
21. 201.148 915.260 ↑ 1.0 145,079 1

Hash Right Join (cost=24,876.19..38,566.90 rows=145,079 width=1,125) (actual time=177.104..915.260 rows=145,079 loops=1)

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

CTE e0

23. 91.622 450.358 ↓ 1.0 110,217 1

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

  • Group Key: tb_cbsd_grants_current.cbsd_id
24. 358.736 358.736 ↓ 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=2.333..358.736 rows=110,280 loops=1)

  • Filter: active
  • Rows Removed by Filter: 12
25. 545.759 545.759 ↓ 1.0 110,217 1

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

26. 64.240 168.353 ↑ 1.0 145,079 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 3129kB
27. 104.113 104.113 ↑ 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=20.643..104.113 rows=145,079 loops=1)

  • Filter: active
28.          

SubPlan (forSubquery Scan)

29. 75.794 75.794 ↓ 725.4 145,079 1

CTE Scan on e0 (cost=0.00..4.00 rows=200 width=218) (actual time=0.843..75.794 rows=145,079 loops=1)

30. 107,973.536 107,973.536 ↓ 1.7 124,005 1

CTE Scan on e1 (cost=0.00..1,455.12 rows=72,756 width=356) (actual time=5,594.382..107,973.536 rows=124,005 loops=1)