explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XNgj

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 2,853.442 ↑ 1.0 1 1

Limit (cost=2,151.79..2,151.80 rows=1 width=11) (actual time=2,853.390..2,853.442 rows=1 loops=1)

  • Output: c0_.party_id, c0_.party_status_id
  • Buffers: shared hit=577408
2. 0.043 2,853.441 ↑ 1.0 1 1

Unique (cost=2,151.79..2,151.80 rows=1 width=11) (actual time=2,853.388..2,853.441 rows=1 loops=1)

  • Output: c0_.party_id, c0_.party_status_id
  • Buffers: shared hit=577408
3. 0.103 2,853.398 ↓ 149.0 149 1

Sort (cost=2,151.79..2,151.79 rows=1 width=11) (actual time=2,853.387..2,853.398 rows=149 loops=1)

  • Output: c0_.party_id, c0_.party_status_id
  • Sort Key: c0_.party_id DESC, c0_.party_status_id
  • Sort Method: quicksort Memory: 31kB
  • Buffers: shared hit=577408
4. 2,015.364 2,853.295 ↓ 149.0 149 1

Nested Loop Left Join (cost=1,364.12..2,151.78 rows=1 width=11) (actual time=2,796.947..2,853.295 rows=149 loops=1)

  • Output: c0_.party_id, c0_.party_status_id
  • Filter: ((lower((c0_.party_name)::text) ~~ '%bitewaw@web-experts.net%'::text) OR (lower((c0_.party_long_name)::text) ~~ '%bitewaw@web-experts.net%'::text) OR (lower((c3_.account_name)::text) ~~ '%bitewaw@web-experts.net%'::text) OR (lower((s4_.display_name)::text) ~~ '%bitewaw@web-experts.net%'::text) OR (lower((s5_.display_name)::text) ~~ '%bitewaw@web-experts.net%'::text))
  • Rows Removed by Filter: 695681
  • Buffers: shared hit=577408
5. 1.324 131.071 ↓ 4,284.0 12,852 1

Nested Loop Left Join (cost=1,363.54..2,149.78 rows=3 width=201) (actual time=58.595..131.071 rows=12,852 loops=1)

  • Output: c0_.party_id, c0_.party_status_id, c0_.party_name, c0_.party_long_name, c1_.account_id, s4_.display_name
  • Buffers: shared hit=39871
6. 18.995 91.191 ↓ 4,284.0 12,852 1

Hash Left Join (cost=1,363.26..2,148.87 rows=3 width=185) (actual time=58.580..91.191 rows=12,852 loops=1)

  • Output: c0_.party_id, c0_.party_status_id, c0_.party_name, c0_.party_long_name, c1_.account_id, c2_.site_id
  • Hash Cond: (c0_.party_id = (c1_.party_id)::numeric)
  • Filter: ((c2_.role_id = ANY ('{46,50,46,50}'::integer[])) OR (c1_.role_id = ANY ('{46,50,46,50}'::integer[])))
  • Rows Removed by Filter: 14769
  • Buffers: shared hit=1321
7. 5.323 45.653 ↓ 1,543.0 7,715 1

Hash Left Join (cost=735.36..1,451.97 rows=5 width=185) (actual time=31.579..45.653 rows=7,715 loops=1)

  • Output: c0_.party_id, c0_.party_status_id, c0_.party_name, c0_.party_long_name, c2_.site_id, c2_.role_id
  • Hash Cond: (c0_.party_id = (c2_.party_id)::numeric)
  • Buffers: shared hit=941
8. 10.126 10.126 ↓ 161.5 323 1

Seq Scan on public.ct_party c0_ (cost=0.00..685.56 rows=2 width=177) (actual time=0.897..10.126 rows=323 loops=1)

  • Output: c0_.party_id, c0_.party_name, c0_.email, c0_.party_type_id, c0_.party_status_id, c0_.language, c0_.eff_date, c0_.exp_date, c0_.add_date, c0_.add_by, c0_.mod_date, c0_.mod_by, c0_.call_user_id, c0_.call_agent_id, c0_.manager_id, c0_.lang_id, c0_.party_long_name, c0_.access_rights_version, c0_.force_session_update_date
  • Filter: ((c0_.party_status_id = ANY ('{1,4}'::numeric[])) AND (c0_.party_id <> '13136'::numeric) AND ((c0_.access_rights_version)::text = 'NEW_ACCESS_RIGHTS_ONLY'::text))
  • Rows Removed by Filter: 23455
  • Buffers: shared hit=485
9. 20.208 30.204 ↑ 1.0 62,005 1

Hash (cost=518.08..518.08 rows=62,080 width=12) (actual time=30.204..30.204 rows=62,005 loops=1)

  • Output: c2_.party_id, c2_.site_id, c2_.role_id
  • Buckets: 65536 Batches: 1 Memory Usage: 3177kB
  • Buffers: shared hit=456
10. 9.996 9.996 ↑ 1.0 62,005 1

Seq Scan on public.ct_party_role_site c2_ (cost=0.00..518.08 rows=62,080 width=12) (actual time=0.012..9.996 rows=62,005 loops=1)

  • Output: c2_.party_id, c2_.site_id, c2_.role_id
  • Buffers: shared hit=456
11. 18.054 26.543 ↓ 1.0 56,101 1

Hash (cost=435.09..435.09 rows=55,088 width=12) (actual time=26.543..26.543 rows=56,101 loops=1)

  • Output: c1_.party_id, c1_.account_id, c1_.role_id
  • Buckets: 65536 Batches: 1 Memory Usage: 2923kB
  • Buffers: shared hit=380
12. 8.489 8.489 ↓ 1.0 56,101 1

Seq Scan on public.ct_party_role_account c1_ (cost=0.00..435.09 rows=55,088 width=12) (actual time=0.009..8.489 rows=56,101 loops=1)

  • Output: c1_.party_id, c1_.account_id, c1_.role_id
  • Buffers: shared hit=380
13. 38.556 38.556 ↑ 1.0 1 12,852

Index Scan using id_d_a_idx on public.site s4_ (cost=0.29..0.30 rows=1 width=24) (actual time=0.002..0.003 rows=1 loops=12,852)

  • Output: s4_.id, s4_.display_name
  • Index Cond: (c2_.site_id = s4_.id)
  • Buffers: shared hit=38550
14. 115.668 706.860 ↓ 54.0 54 12,852

Nested Loop Left Join (cost=0.58..0.64 rows=1 width=40) (actual time=0.009..0.055 rows=54 loops=12,852)

  • Output: c3_.account_id, c3_.account_name, s5_.display_name
  • Buffers: shared hit=537537
15. 38.556 38.556 ↑ 1.0 1 12,852

Index Scan using ct_account_pkey on public.ct_account c3_ (cost=0.29..0.30 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=12,852)

  • Output: c3_.account_id, c3_.account_name, c3_.account_type_id, c3_.lang_id, c3_.account_status_id, c3_.eff_date, c3_.exp_date, c3_.created_date, c3_.owner_id, c3_.parent_account_id, c3_.tm_ref_id, c3_.billing_client_id
  • Index Cond: ((c1_.account_id)::numeric = c3_.account_id)
  • Buffers: shared hit=38556
16. 552.636 552.636 ↓ 27.0 54 12,852

Index Scan using ix_site_account_id on public.site s5_ (cost=0.29..0.33 rows=2 width=26) (actual time=0.006..0.043 rows=54 loops=12,852)

  • Output: s5_.account_id, s5_.display_name
  • Index Cond: (c3_.account_id = s5_.account_id)
  • Buffers: shared hit=498981