explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qjfn

Settings
# exclusive inclusive rows x rows loops node
1. 1.659 3,550.894 ↑ 1.2 3,873 1

Sort (cost=48,089.23..48,100.39 rows=4,464 width=37) (actual time=3,550.705..3,550.894 rows=3,873 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 434kB
2. 153.219 3,549.235 ↑ 1.2 3,873 1

GroupAggregate (cost=47,718.18..47,818.62 rows=4,464 width=37) (actual time=3,389.139..3,549.235 rows=3,873 loops=1)

  • Group Key: contacts_values.value__norm
  • Filter: (count(*) > 2)
  • Rows Removed by Filter: 42,074
3. 458.514 3,396.016 ↓ 31.7 141,395 1

Sort (cost=47,718.18..47,729.34 rows=4,464 width=34) (actual time=3,389.062..3,396.016 rows=141,395 loops=1)

  • Sort Key: contacts_values.value__norm
  • Sort Method: quicksort Memory: 18,558kB
4. 12.219 2,937.502 ↓ 31.7 141,395 1

Result (cost=26,967.68..47,447.57 rows=4,464 width=34) (actual time=2,110.877..2,937.502 rows=141,395 loops=1)

  • One-Time Filter: ((current_setting('nimble.company_id'::text))::bytea = '\x4c2118ad54397f271b000000'::bytea)
5. 689.857 2,925.283 ↓ 31.7 141,395 1

Hash Join (cost=26,967.68..47,447.57 rows=4,464 width=34) (actual time=2,110.874..2,925.283 rows=141,395 loops=1)

  • Hash Cond: (contacts_values.contact_id = contacts.id)
6. 131.882 131.882 ↑ 1.4 141,395 1

Index Only Scan using ix_contacts_values_value__norm on contacts_values (cost=0.69..19,945.79 rows=203,728 width=47) (actual time=0.060..131.882 rows=141,395 loops=1)

  • Index Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (field_name = 'title'::text) AND (field_id = '\x000000000000000000000000'::bytea))
  • Heap Fetches: 16,546
7. 588.108 2,103.544 ↓ 40.2 1,052,168 1

Hash (cost=26,639.91..26,639.91 rows=26,166 width=26) (actual time=2,103.543..2,103.544 rows=1,052,168 loops=1)

  • Buckets: 524,288 (originally 32768) Batches: 4 (originally 1) Memory Usage: 28,673kB
8. 1,334.590 1,515.436 ↓ 40.2 1,052,168 1

Bitmap Heap Scan on contacts (cost=728.77..26,639.91 rows=26,166 width=26) (actual time=188.148..1,515.436 rows=1,052,168 loops=1)

  • Recheck Cond: (((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (owner_id IS NOT NULL) AND (owner_id = (current_setting('nimble.user_id'::text))::bytea)) OR ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (read_for_users && '{""\\x00""}'::bytea[])) OR ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (read_for_users && ARRAY[(current_setting('nimble.user_id'::text))::bytea])) OR ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (read_for_groups && (string_to_array(current_setting('nimble.group_ids'::text), ','::text))::bytea[])) OR ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (edit_for_users && ARRAY[(current_setting('nimble.user_id'::text))::bytea])) OR ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (edit_for_groups && (string_to_array(current_setting('nimble.group_ids'::text), ','::text))::bytea[])))
  • Filter: (((owner_id IS NOT NULL) AND (owner_id = (current_setting('nimble.user_id'::text))::bytea)) OR (read_for_users && '{""\\x00""}'::bytea[]) OR (read_for_users && ARRAY[(current_setting('nimble.user_id'::text))::bytea]) OR (read_for_groups && (string_to_array(current_setting('nimble.group_ids'::text), ','::text))::bytea[]) OR (edit_for_users && ARRAY[(current_setting('nimble.user_id'::text))::bytea]) OR (edit_for_groups && (string_to_array(current_setting('nimble.group_ids'::text), ','::text))::bytea[]))
  • Heap Blocks: exact=49,782
9. 0.001 180.846 ↓ 0.0 0 1

BitmapOr (cost=728.77..728.77 rows=26,430 width=0) (actual time=180.846..180.846 rows=0 loops=1)

10. 0.015 0.015 ↓ 0.0 0 1

Bitmap Index Scan on ix_company_id_owner_id (cost=0.00..1.81 rows=12 width=0) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (owner_id IS NOT NULL) AND (owner_id = (current_setting('nimble.user_id'::text))::bytea))
11. 177.583 177.583 ↓ 199.4 1,053,382 1

Bitmap Index Scan on ix_company_rls_permissions (cost=0.00..137.53 rows=5,283 width=0) (actual time=177.582..177.583 rows=1,053,382 loops=1)

  • Index Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (read_for_users && '{""\\x00""}'::bytea[]))
12. 0.863 0.863 ↓ 0.0 0 1

Bitmap Index Scan on ix_company_rls_permissions (cost=0.00..137.54 rows=5,283 width=0) (actual time=0.863..0.863 rows=0 loops=1)

  • Index Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (read_for_users && ARRAY[(current_setting('nimble.user_id'::text))::bytea]))
13. 0.800 0.800 ↓ 0.0 0 1

Bitmap Index Scan on ix_company_rls_permissions (cost=0.00..137.54 rows=5,283 width=0) (actual time=0.800..0.800 rows=0 loops=1)

  • Index Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (read_for_groups && (string_to_array(current_setting('nimble.group_ids'::text), ','::text))::bytea[]))
14. 0.774 0.774 ↓ 0.0 0 1

Bitmap Index Scan on ix_company_rls_permissions (cost=0.00..137.54 rows=5,283 width=0) (actual time=0.774..0.774 rows=0 loops=1)

  • Index Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (edit_for_users && ARRAY[(current_setting('nimble.user_id'::text))::bytea]))
15. 0.810 0.810 ↓ 0.0 0 1

Bitmap Index Scan on ix_company_rls_permissions (cost=0.00..137.54 rows=5,283 width=0) (actual time=0.810..0.810 rows=0 loops=1)

  • Index Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (edit_for_groups && (string_to_array(current_setting('nimble.group_ids'::text), ','::text))::bytea[]))
Planning time : 1.001 ms
Execution time : 3,564.234 ms