explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IbkN

Settings
# exclusive inclusive rows x rows loops node
1. 1,210.170 13,431.909 ↓ 2.0 145,079 1

Append (cost=49.99..320,491.28 rows=72,669 width=566) (actual time=4,834.842..13,431.909 rows=145,079 loops=1)

2.          

CTE e0

3. 0.001 0.574 ↓ 0.0 0 1

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

4. 0.566 0.573 ↓ 0.0 0 1

Sort (cost=49.24..49.54 rows=120 width=534) (actual time=0.573..0.573 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.001 0.007 ↓ 0.0 0 1

Append (cost=0.00..45.10 rows=120 width=534) (actual time=0.007..0.007 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.001 0.001 ↓ 0.0 0 1

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

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

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

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

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

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

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

11. 0.000 0.575 ↓ 0.0 0 1

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

12. 0.575 0.575 ↓ 0.0 0 1

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

13. 0.000 0.000 ↓ 0.0 0

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

14. 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)
15. 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)
16. 999.742 12,221.163 ↓ 2.0 145,079 1

Subquery Scan on *SELECT* 2 (cost=24,878.89..41,108.49 rows=72,540 width=73) (actual time=4,834.264..12,221.163 rows=145,079 loops=1)

17. 798.826 11,221.421 ↓ 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=4,834.262..11,221.421 rows=145,079 loops=1)

  • Filter: (NOT (hashed SubPlan 2))
18. 2,018.673 10,422.594 ↑ 1.0 145,079 1

Hash Right Join (cost=24,876.19..38,566.90 rows=145,079 width=1,125) (actual time=4,833.954..10,422.594 rows=145,079 loops=1)

  • Hash Cond: ((e0.cbsd_id)::text = (a_1.cbsd_id)::text)
19.          

CTE e0

20. 1,010.812 2,576.197 ↓ 1.0 110,217 1

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

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

  • Filter: active
  • Rows Removed by Filter: 12
22. 3,571.077 3,571.077 ↓ 1.0 110,217 1

CTE Scan on e0 (cost=0.00..2,202.80 rows=110,140 width=226) (actual time=0.942..3,571.077 rows=110,217 loops=1)

23. 1,066.412 4,832.844 ↑ 1.0 145,079 1

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

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

  • Filter: active
25.          

SubPlan (forSubquery Scan)

26. 0.001 0.001 ↓ 0.0 0 1

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