explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FNfn

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 2,129.691 ↓ 0.0 0 1

Limit (cost=8,274.05..9,038.52 rows=1 width=318) (actual time=2,129.691..2,129.691 rows=0 loops=1)

2.          

Initplan (for Limit)

3. 0.052 0.052 ↓ 0.0 0 1

Index Only Scan using uk_property_preferences_cid_property_id_key on property_preferences pp (cost=0.42..1.47 rows=1 width=0) (actual time=0.052..0.052 rows=0 loops=1)

  • Index Cond: ((cid = 1,826) AND (property_id = 928,906) AND (key = 'HIDE_LIVE_CHAT'::text))
  • Heap Fetches: 0
4. 0.050 0.050 ↓ 0.0 0 1

Index Only Scan using uk_website_preferences_cid_website_id_key_is_preview on website_preferences wp (cost=0.42..1.46 rows=1 width=0) (actual time=0.050..0.050 rows=0 loops=1)

  • Index Cond: ((cid = 1,826) AND (website_id = 25,931) AND (key = 'HIDE_LIVE_CHAT'::text))
  • Heap Fetches: 0
5. 0.008 0.008 ↓ 0.0 0 1

Index Scan using uk_website_preferences_cid_website_id_key_is_preview on website_preferences wp_1 (cost=0.42..2.46 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1)

  • Index Cond: ((cid = 1,826) AND (website_id = 25,931) AND ((key)::text = 'WEBSITE_CHAT_HTML'::text))
  • Filter: (value IS NOT NULL)
6. 0.113 2,129.689 ↓ 0.0 0 1

Result (cost=8,268.67..850,716.81 rows=1,102 width=318) (actual time=2,129.689..2,129.689 rows=0 loops=1)

  • One-Time Filter: ((NOT $0) AND (NOT $1) AND (NOT $2))
7. 1,088.029 2,129.576 ↓ 0.0 0 1

Nested Loop Left Join (cost=8,268.67..850,716.81 rows=1,102 width=318) (actual time=2,129.576..2,129.576 rows=0 loops=1)

  • Join Filter: ((cupr.cid = cup.cid) AND (cupr.company_user_id = cup.company_user_id))
  • Rows Removed by Join Filter: 12,128,417
  • Filter: ((cu.is_administrator = 1) OR (cupr.property_id = 928,906))
  • Rows Removed by Filter: 436
8. 0.195 5.974 ↑ 1.5 33 1

Nested Loop (cost=0.71..1,449.17 rows=50 width=326) (actual time=1.688..5.974 rows=33 loops=1)

9. 4.934 4.934 ↑ 1.5 65 1

Index Scan using uk_company_user_preferences_cid_company_user_id_key on company_user_preferences cup (cost=0.42..1,254.01 rows=96 width=8) (actual time=1.649..4.934 rows=65 loops=1)

  • Index Cond: ((cid = 1,826) AND ((key)::text = 'IS_AVAILABLE_FOR_CHAT'::text))
  • Filter: (value = '1'::text)
10. 0.845 0.845 ↑ 1.0 1 65

Index Scan using pk_company_users on company_users cu (cost=0.29..2.03 rows=1 width=318) (actual time=0.013..0.013 rows=1 loops=65)

  • Index Cond: ((cid = 1,826) AND (id = cup.company_user_id))
  • Filter: ((default_company_user_id IS NULL) AND (deleted_on IS NULL) AND (is_disabled = 0) AND (company_user_type_id = 2))
  • Rows Removed by Filter: 0
11. 638.953 1,035.573 ↑ 1.0 367,541 33

Materialize (cost=8,267.96..33,257.66 rows=384,457 width=12) (actual time=7.607..31.381 rows=367,541 loops=33)

12. 36.485 396.620 ↑ 1.0 367,541 1

Subquery Scan on cupr (cost=8,267.96..31,335.38 rows=384,457 width=12) (actual time=251.029..396.620 rows=367,541 loops=1)

13. 253.297 360.135 ↑ 1.0 367,541 1

HashAggregate (cost=8,267.96..19,801.67 rows=384,457 width=12) (actual time=251.028..360.135 rows=367,541 loops=1)

  • Group Key: cupg.cid, cupg.company_user_id, pga.property_id
14. 56.628 106.838 ↑ 1.0 368,252 1

Merge Join (cost=3.55..5,384.53 rows=384,457 width=12) (actual time=0.114..106.838 rows=368,252 loops=1)

  • Merge Cond: (pg.id = cupg.property_group_id)
15. 1.306 4.114 ↑ 1.4 5,735 1

Merge Join (cost=0.56..940.79 rows=7,847 width=20) (actual time=0.080..4.114 rows=5,735 loops=1)

  • Merge Cond: (pg.id = pga.property_group_id)
16. 1.622 1.622 ↑ 3.5 1,486 1

Index Scan using pk_property_groups on property_groups pg (cost=0.28..289.33 rows=5,146 width=8) (actual time=0.026..1.622 rows=1,486 loops=1)

  • Index Cond: (cid = 1,826)
  • Filter: ((deleted_by IS NULL) AND (deleted_on IS NULL))
17. 1.186 1.186 ↑ 1.4 5,735 1

Index Only Scan using idx_property_group_associations_master on property_group_associations pga (cost=0.28..383.37 rows=7,949 width=12) (actual time=0.052..1.186 rows=5,735 loops=1)

  • Index Cond: (cid = 1,826)
  • Heap Fetches: 56
18. 23.218 46.096 ↓ 7.3 368,252 1

Materialize (cost=0.29..2,564.80 rows=50,633 width=12) (actual time=0.018..46.096 rows=368,252 loops=1)

19. 22.878 22.878 ↑ 1.0 50,631 1

Index Scan using idx_company_user_property_groups_property_group_id on company_user_property_groups cupg (cost=0.29..2,438.22 rows=50,633 width=12) (actual time=0.014..22.878 rows=50,631 loops=1)

  • Filter: (cid = 1,826)
  • Rows Removed by Filter: 8
Planning time : 4.523 ms
Execution time : 2,145.727 ms