explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eJA3

Settings
# exclusive inclusive rows x rows loops node
1. 0.006 5,468.555 ↑ 1.0 1 1

Nested Loop (cost=52,817.47..52,817.52 rows=1 width=40) (actual time=5,468.552..5,468.555 rows=1 loops=1)

2.          

CTE e0

3. 298.654 4,831.505 ↓ 5.4 137,543 1

Merge Left Join (cost=39,979.13..51,193.25 rows=25,332 width=324) (actual time=2,684.713..4,831.505 rows=137,543 loops=1)

  • Merge Cond: ((a.cbsd_id)::text = (tb_cbsd_grants_current_1.cbsd_id)::text)
4. 2,547.808 3,380.411 ↓ 2,990.1 137,543 1

Sort (cost=39,978.71..39,978.82 rows=46 width=292) (actual time=2,683.738..3,380.411 rows=137,543 loops=1)

  • Sort Key: a.cbsd_id
  • Sort Method: external merge Disk: 39360kB
5. 195.448 832.603 ↓ 2,990.1 137,543 1

Hash Right Join (cost=37,360.23..39,976.98 rows=46 width=292) (actual time=423.009..832.603 rows=137,543 loops=1)

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

CTE e0

7. 83.310 142.051 ↓ 1.0 110,217 1

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

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

  • Filter: active
  • Rows Removed by Filter: 12
9. 214.530 214.530 ↓ 1.0 110,217 1

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

10. 155.559 422.625 ↓ 2,990.1 137,543 1

Hash (cost=27,223.74..27,223.74 rows=46 width=252) (actual time=422.625..422.625 rows=137,543 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 16 (originally 1) Memory Usage: 3969kB
11. 180.835 267.066 ↓ 2,990.1 137,543 1

Gather (cost=1,000.00..27,223.74 rows=46 width=252) (actual time=3.238..267.066 rows=137,543 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 86.231 86.231 ↓ 2,413.1 45,848 3

Parallel Seq Scan on tb_cbsd_status_current a (cost=0.00..26,219.14 rows=19 width=252) (actual time=0.954..86.231 rows=45,848 loops=3)

  • Filter: (active AND ('0103000020E610000001000000050000000000000000005FC000000000000039400000000000005FC0000000000080484000000000008050C0000000000080484000000000008050C000000000000039400000000000005FC00000000000003940'::geometry ~ location) AND ((owner_id)::text = 'ZLMHlO'::text) AND _st_contains('0103000020E610000001000000050000000000000000005FC000000000000039400000000000005FC0000000000080484000000000008050C0000000000080484000000000008050C000000000000039400000000000005FC00000000000003940'::geometry, location))
  • Rows Removed by Filter: 2512
13. 1,064.078 1,152.440 ↑ 1.0 110,075 1

GroupAggregate (cost=0.42..9,584.24 rows=110,140 width=54) (actual time=0.084..1,152.440 rows=110,075 loops=1)

  • Group Key: tb_cbsd_grants_current_1.cbsd_id
14. 88.362 88.362 ↑ 1.0 110,127 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.023..88.362 rows=110,127 loops=1)

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

CTE e1

16. 38.774 5,206.268 ↑ 1.0 1 1

Aggregate (cost=569.97..569.98 rows=1 width=8) (actual time=5,206.267..5,206.268 rows=1 loops=1)

17. 5,167.494 5,167.494 ↓ 5.4 137,543 1

CTE Scan on e0 e0_1 (cost=0.00..506.64 rows=25,332 width=0) (actual time=2,684.717..5,167.494 rows=137,543 loops=1)

18.          

CTE e2

19. 0.208 262.272 ↑ 1.0 1 1

Aggregate (cost=1,054.23..1,054.24 rows=1 width=32) (actual time=262.272..262.272 rows=1 loops=1)

20. 0.009 262.064 ↑ 1.0 10 1

Limit (cost=1,054.06..1,054.08 rows=10 width=1,668) (actual time=262.055..262.064 rows=10 loops=1)

21. 173.238 262.055 ↑ 2,533.2 10 1

Sort (cost=1,054.06..1,117.39 rows=25,332 width=1,668) (actual time=262.052..262.055 rows=10 loops=1)

  • Sort Key: e0_2.cbsd_id
  • Sort Method: top-N heapsort Memory: 30kB
22. 88.817 88.817 ↓ 5.4 137,543 1

CTE Scan on e0 e0_2 (cost=0.00..506.64 rows=25,332 width=1,668) (actual time=0.015..88.817 rows=137,543 loops=1)

23. 5,206.271 5,206.271 ↑ 1.0 1 1

CTE Scan on e1 (cost=0.00..0.02 rows=1 width=8) (actual time=5,206.270..5,206.271 rows=1 loops=1)

24. 262.278 262.278 ↑ 1.0 1 1

CTE Scan on e2 (cost=0.00..0.02 rows=1 width=32) (actual time=262.277..262.278 rows=1 loops=1)