explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GRnR

Settings
# exclusive inclusive rows x rows loops node
1. 2.233 11.600 ↓ 0.0 0 1

Insert on temp_posting (cost=1,136.74..11,193.18 rows=6 width=9) (actual time=11.600..11.600 rows=0 loops=1)

2.          

CTE insert_private

3. 0.136 1.316 ↓ 0.0 0 1

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

4. 0.123 1.180 ↓ 3.0 413 1

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

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

Bitmap Heap Scan on posting_link pl (cost=19.05..793.95 rows=755 width=8) (actual time=0.072..0.349 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.059 0.059 ↓ 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.059..0.059 rows=885 loops=1)

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

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

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

Seq Scan on posting p_1 (cost=0.00..201.37 rows=957 width=8) (actual time=0.008..0.618 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.011 0.224 ↓ 1.9 13 1

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

  • Group Key: cc.business_id
11. 0.017 0.213 ↓ 2.9 20 1

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

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

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

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

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

14. 0.017 0.088 ↓ 1.2 20 1

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

15. 0.015 0.015 ↑ 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.008..0.015 rows=14 loops=1)

  • Index Cond: (business_id = '1703858125129385245'::bigint)
16. 0.056 0.056 ↑ 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.003..0.004 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.040 0.040 ↑ 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.002 rows=1 loops=20)

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

Nested Loop (cost=0.28..10,056.72 rows=6 width=9) (actual time=0.236..9.367 rows=4,100 loops=1)

20. 0.228 0.228 ↓ 1.9 13 1

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

21. 4.441 8.541 ↓ 315.0 315 13

Index Scan using idx_posting_business_id_status_is_public on posting p (cost=0.28..1,436.64 rows=1 width=16) (actual time=0.014..0.657 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. 0.000 4.100 ↑ 1.0 1 4,100

Aggregate (cost=8.31..8.32 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=4,100)

24. 4.100 4.100 ↓ 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.001..0.001 rows=0 loops=4,100)

  • Index Cond: ((posting_id = p.id) AND (status = 1905))
  • Filter: (posted_to_user_id = '1693876186242876624'::bigint)