explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3oI

Settings
# exclusive inclusive rows x rows loops node
1. 11.631 6,957.958 ↓ 0.0 0 1

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

2.          

CTE insert_private

3. 0.167 1.605 ↓ 0.0 0 1

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

4. 0.139 1.438 ↓ 3.0 413 1

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

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

Bitmap Heap Scan on posting_link pl (cost=19.05..793.95 rows=755 width=8) (actual time=0.104..0.403 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.087 0.087 ↓ 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.087..0.087 rows=885 loops=1)

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

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

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

Seq Scan on posting p_1 (cost=0.00..201.37 rows=957 width=8) (actual time=0.011..0.801 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.007 0.171 ↓ 1.9 13 1

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

  • Group Key: cc.business_id
11. 0.021 0.164 ↓ 2.9 20 1

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

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

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

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

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

14. 0.011 0.054 ↓ 1.2 20 1

Nested Loop (cost=0.56..115.81 rows=16 width=32) (actual time=0.011..0.054 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.007..0.015 rows=14 loops=1)

  • Index Cond: (business_id = '1703858125129385245'::bigint)
16. 0.028 0.028 ↑ 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.001..0.002 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. 2.264 6,946.327 ↓ 683.3 4,100 1

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

20. 0.178 0.178 ↓ 1.9 13 1

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

21. 14.885 6,943.885 ↓ 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=0.994..534.145 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 6,929.000 ↑ 1.0 1 4,100

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

24. 24.600 6,920.800 ↓ 0.0 0 4,100

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

25. 12.300 12.300 ↓ 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.003..0.003 rows=0 loops=4,100)

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

Seq Scan on posting_link_history pl_2 (cost=0.00..711.97 rows=1 width=8) (actual time=1.679..1.679 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