explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k384

Settings
# exclusive inclusive rows x rows loops node
1. 345.071 764.770 ↓ 7.9 134,161 1

Bitmap Heap Scan on sent_mails (cost=52,983.40..101,057.62 rows=17,018 width=3,161) (actual time=432.339..764.770 rows=134,161 loops=1)

  • Recheck Cond: ((((contacts_permissions_cache && '{69061}'::integer[]) OR (contacts_company_permissions_cache && '{69060}'::integer[])) OR (((resource_type)::text = 'UserAccount'::text) AND (user_accounts_company_id = 16782)) OR (((resource_type)::text = 'Borrower'::text) AND (urla_forms_permissions_cache && $3))) AND (created_at >= '2019-01-01 08:00:00'::timestamp without time zone) AND (created_at <= '2019-03-19 06:59:59'::timestamp without time zone))
  • Filter: ((((resource_type)::text = 'Contact'::text) AND ((contacts_permissions_cache && '{69061}'::integer[]) OR (contacts_company_permissions_cache && '{69060}'::integer[]))) OR (((resource_type)::text = 'UserAccount'::text) AND (user_accounts_company_id = 16782)) OR (((resource_type)::text = 'Borrower'::text) AND (urla_forms_permissions_cache && $3)))
  • Heap Blocks: exact=52017
2.          

Initplan (for Bitmap Heap Scan)

3. 0.046 0.975 ↑ 1.0 1 1

Aggregate (cost=734.54..734.55 rows=1 width=4) (actual time=0.975..0.975 rows=1 loops=1)

4.          

CTE roles

5. 0.120 0.859 ↑ 2.7 225 1

Recursive Union (cost=0.42..720.99 rows=602 width=4) (actual time=0.073..0.859 rows=225 loops=1)

6. 0.069 0.069 ↑ 1.0 2 1

Index Only Scan using idx_resource_membership_dual_uuid on resources_memberships (cost=0.42..3.45 rows=2 width=4) (actual time=0.069..0.069 rows=2 loops=1)

  • Index Cond: (resource_uuid = 69061)
  • Heap Fetches: 0
7. 0.206 0.670 ↓ 3.8 226 2

Nested Loop (cost=0.42..70.55 rows=60 width=4) (actual time=0.009..0.335 rows=226 loops=2)

8. 0.014 0.014 ↓ 5.6 112 2

WorkTable Scan on roles (cost=0.00..0.40 rows=20 width=4) (actual time=0.001..0.007 rows=112 loops=2)

9. 0.450 0.450 ↑ 1.5 2 225

Index Only Scan using idx_resource_membership_dual_uuid on resources_memberships resources_memberships_1 (cost=0.42..3.48 rows=3 width=8) (actual time=0.002..0.002 rows=2 loops=225)

  • Index Cond: (resource_uuid = roles.membership_uuid)
  • Heap Fetches: 22
10. 0.929 0.929 ↑ 2.7 225 1

CTE Scan on roles roles_1 (cost=0.00..12.04 rows=602 width=4) (actual time=0.076..0.929 rows=225 loops=1)

11. 23.450 418.724 ↓ 0.0 0 1

BitmapAnd (cost=52,248.85..52,248.85 rows=17,086 width=0) (actual time=418.724..418.724 rows=0 loops=1)

12. 0.061 48.745 ↓ 0.0 0 1

BitmapOr (cost=15,243.32..15,243.32 rows=167,828 width=0) (actual time=48.745..48.745 rows=0 loops=1)

13. 0.000 42.688 ↓ 0.0 0 1

BitmapOr (cost=4,964.94..4,964.94 rows=167,658 width=0) (actual time=42.688..42.688 rows=0 loops=1)

14. 0.675 0.675 ↑ 33.1 2,536 1

Bitmap Index Scan on idx_sent_mails_contacts_permissions_cache (cost=0.00..724.72 rows=83,829 width=0) (actual time=0.675..0.675 rows=2,536 loops=1)

  • Index Cond: (contacts_permissions_cache && '{69061}'::integer[])
15. 42.013 42.013 ↓ 2.4 204,939 1

Bitmap Index Scan on idx_sent_mails_contacts_company_permissions_cache (cost=0.00..4,231.72 rows=83,829 width=0) (actual time=42.013..42.013 rows=204,939 loops=1)

  • Index Cond: (contacts_company_permissions_cache && '{69060}'::integer[])
16. 0.199 1.409 ↓ 0.0 0 1

BitmapAnd (cost=1,576.11..1,576.11 rows=53 width=0) (actual time=1.409..1.409 rows=0 loops=1)

17. 1.197 1.197 ↑ 1.7 6,082 1

Bitmap Index Scan on index_sent_mails_on_resource_type_and_resource_id (cost=0.00..248.19 rows=10,618 width=0) (actual time=1.197..1.197 rows=6,082 loops=1)

  • Index Cond: ((resource_type)::text = 'UserAccount'::text)
18. 0.013 0.013 ↓ 0.0 0 1

Bitmap Index Scan on idx_sent_mails_user_accounts_company_id (cost=0.00..1,319.15 rows=83,829 width=0) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (user_accounts_company_id = 16782)
19. 0.590 4.587 ↓ 0.0 0 1

BitmapAnd (cost=8,701.77..8,701.77 rows=117 width=0) (actual time=4.587..4.587 rows=0 loops=1)

20. 2.435 2.435 ↓ 1.0 12,202 1

Bitmap Index Scan on index_sent_mails_on_resource_type_and_resource_id (cost=0.00..271.58 rows=11,736 width=0) (actual time=2.435..2.435 rows=12,202 loops=1)

  • Index Cond: ((resource_type)::text = 'Borrower'::text)
21. 1.562 1.562 ↓ 0.0 0 1

Bitmap Index Scan on idx_sent_mails_urla_forms_permissions_cache (cost=0.00..8,421.43 rows=167,658 width=0) (actual time=1.562..1.562 rows=0 loops=1)

  • Index Cond: (urla_forms_permissions_cache && $3)
22. 346.529 346.529 ↓ 1.1 1,893,721 1

Bitmap Index Scan on index_sent_mails_on_created_at (cost=0.00..37,001.02 rows=1,706,859 width=0) (actual time=346.529..346.529 rows=1,893,721 loops=1)

  • Index Cond: ((created_at >= '2019-01-01 08:00:00'::timestamp without time zone) AND (created_at <= '2019-03-19 06:59:59'::timestamp without time zone))
Planning time : 12.846 ms
Execution time : 771.004 ms