explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iT5J

Settings
# exclusive inclusive rows x rows loops node
1. 25.680 9,928.565 ↓ 0.0 0 1

Insert on temp_posting (cost=1,136.74..838,502.32 rows=6 width=9) (actual time=9,928.565..9,928.565 rows=0 loops=1)

2.          

CTE insert_private

3. 0.280 1.996 ↓ 0.0 0 1

Insert on temp_posting temp_posting_1 (cost=232.38..1,011.51 rows=140 width=9) (actual time=1.996..1.996 rows=0 loops=1)

4. 0.183 1.716 ↓ 3.0 413 1

Hash Join (cost=232.38..1,011.51 rows=140 width=9) (actual time=1.184..1.716 rows=413 loops=1)

  • Hash Cond: (pl.posting_id = p_1.id)
5. 0.393 0.481 ↓ 1.0 770 1

Bitmap Heap Scan on posting_link pl (cost=19.05..793.95 rows=755 width=8) (actual time=0.110..0.481 rows=770 loops=1)

  • Recheck Cond: (posted_to_user_id = '1693876186242876624'::bigint)
  • Filter: ((status <> 1901) AND (status <> 1905))
  • Rows Removed by Filter: 115
  • Heap Blocks: exact=106
6. 0.088 0.088 ↓ 1.0 885 1

Bitmap Index Scan on idx_posting_link_posted_to_user_id (cost=0.00..18.86 rows=876 width=0) (actual time=0.088..0.088 rows=885 loops=1)

  • Index Cond: (posted_to_user_id = '1693876186242876624'::bigint)
7. 0.136 1.052 ↑ 1.2 802 1

Hash (cost=201.37..201.37 rows=957 width=8) (actual time=1.052..1.052 rows=802 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 40kB
8. 0.916 0.916 ↑ 1.2 802 1

Seq Scan on posting p_1 (cost=0.00..201.37 rows=957 width=8) (actual time=0.014..0.916 rows=802 loops=1)

  • Filter: ((NOT is_public) AND (status <> 1802) AND (status <> 1800))
  • Rows Removed by Filter: 4356
9.          

CTE my_shippers

10. 0.013 0.251 ↓ 1.9 13 1

Group (cost=124.91..124.95 rows=7 width=8) (actual time=0.229..0.251 rows=13 loops=1)

  • Group Key: cc.business_id
11. 0.022 0.238 ↓ 2.9 20 1

Sort (cost=124.91..124.93 rows=7 width=8) (actual time=0.228..0.238 rows=20 loops=1)

  • Sort Key: cc.business_id
  • Sort Method: quicksort Memory: 25kB
12. 0.000 0.216 ↓ 2.9 20 1

Nested Loop (cost=1.13..124.82 rows=7 width=8) (actual time=0.065..0.216 rows=20 loops=1)

  • Join Filter: (ubl.user_id = bu.id)
13. 0.040 0.159 ↓ 2.9 20 1

Nested Loop (cost=0.85..121.97 rows=7 width=24) (actual time=0.043..0.159 rows=20 loops=1)

14. 0.008 0.079 ↓ 1.2 20 1

Nested Loop (cost=0.56..115.81 rows=16 width=32) (actual time=0.033..0.079 rows=20 loops=1)

15. 0.029 0.029 ↑ 1.0 14 1

Index Scan using idx_user_business_link_business_id on user_business_link ubl (cost=0.28..11.47 rows=14 width=8) (actual time=0.023..0.029 rows=14 loops=1)

  • Index Cond: (business_id = '1703858125129385245'::bigint)
16. 0.042 0.042 ↑ 1.0 1 14

Index Scan using idx_carrier_contact_user_id on carrier_contact cc (cost=0.28..7.44 rows=1 width=24) (actual time=0.002..0.003 rows=1 loops=14)

  • Index Cond: (user_id = ubl.user_id)
17. 0.040 0.040 ↑ 1.0 1 20

Index Scan using carrier_contact_business_pkey on carrier_contact_business ccb (cost=0.28..0.37 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=20)

  • Index Cond: (id = cc.carrier_contact_business_id)
  • Filter: (network_type = 4800)
18. 0.060 0.060 ↑ 1.0 1 20

Index Only Scan using idx_business_user_id on business_user bu (cost=0.28..0.39 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=20)

  • Index Cond: (id = cc.user_id)
  • Heap Fetches: 20
19. 3.250 9,902.885 ↓ 683.3 4,100 1

Nested Loop (cost=0.28..837,365.86 rows=6 width=9) (actual time=2.692..9,902.885 rows=4,100 loops=1)

20. 0.265 0.265 ↓ 1.9 13 1

CTE Scan on my_shippers (cost=0.00..0.14 rows=7 width=8) (actual time=0.231..0.265 rows=13 loops=1)

21. 22.470 9,899.370 ↓ 315.0 315 13

Index Scan using idx_posting_business_id_status_is_public on posting p (cost=0.28..119,623.66 rows=1 width=16) (actual time=1.394..761.490 rows=315 loops=13)

  • Index Cond: ((business_id = my_shippers.business_id) AND (is_public = true))
  • Filter: (is_public AND (status <> 1802) AND (status <> 1800) AND ((SubPlan 3) = 0))
  • Rows Removed by Filter: 4
22.          

SubPlan (forIndex Scan)

23. 8.200 9,876.900 ↑ 1.0 1 4,100

Aggregate (cost=720.28..720.29 rows=1 width=8) (actual time=2.408..2.409 rows=1 loops=4,100)

24. 36.900 9,868.700 ↓ 0.0 0 4,100

Append (cost=0.29..720.28 rows=2 width=8) (actual time=2.407..2.407 rows=0 loops=4,100)

25. 20.500 20.500 ↓ 0.0 0 4,100

Index Scan using idx_posting_link_posting_id_status on posting_link pl_1 (cost=0.29..8.31 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=4,100)

  • Index Cond: ((posting_id = p.id) AND (status = 1905))
  • Filter: (posted_to_user_id = '1693876186242876624'::bigint)
26. 9,811.300 9,811.300 ↓ 0.0 0 4,100

Seq Scan on posting_link_history pl_2 (cost=0.00..711.97 rows=1 width=8) (actual time=2.393..2.393 rows=0 loops=4,100)

  • Filter: ((posting_id = p.id) AND (status = 1905) AND (posted_to_user_id = '1693876186242876624'::bigint))
  • Rows Removed by Filter: 18341