explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zb5

Settings
# exclusive inclusive rows x rows loops node
1. 1.965 10,002.720 ↑ 1.2 3,873 1

Sort (cost=99,477.99..99,489.15 rows=4,464 width=48) (actual time=10,002.404..10,002.720 rows=3,873 loops=1)

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

GroupAggregate (cost=97,968.62..99,207.38 rows=4,464 width=48) (actual time=9,820.085..10,000.755 rows=3,873 loops=1)

  • Group Key: (nimble_contact_value_normalize(CASE WHEN (length(contacts_values.value) < 1024) THEN contacts_values.value ELSE NULL::text END))
  • Filter: (count(*) > 2)
  • Rows Removed by Filter: 42,074
3. 638.966 9,829.740 ↓ 31.7 141,395 1

Sort (cost=97,968.62..97,979.78 rows=4,464 width=45) (actual time=9,820.040..9,829.740 rows=141,395 loops=1)

  • Sort Key: (nimble_contact_value_normalize(CASE WHEN (length(contacts_values.value) < 1024) THEN contacts_values.value ELSE NULL::text END))
  • Sort Method: quicksort Memory: 18,558kB
4. 20.124 9,190.774 ↓ 31.7 141,395 1

Result (cost=729.34..97,698.01 rows=4,464 width=45) (actual time=198.666..9,190.774 rows=141,395 loops=1)

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

Nested Loop (cost=729.34..97,698.01 rows=4,464 width=45) (actual time=198.662..9,170.650 rows=141,395 loops=1)

6. 1,206.608 1,397.216 ↓ 40.2 1,052,168 1

Bitmap Heap Scan on contacts (cost=728.77..26,639.91 rows=26,166 width=26) (actual time=198.467..1,397.216 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
7. 0.003 190.608 ↓ 0.0 0 1

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

8. 0.020 0.020 ↓ 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.020..0.020 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))
9. 186.915 186.915 ↓ 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=186.915..186.915 rows=1,053,382 loops=1)

  • Index Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (read_for_users && '{""\\x00""}'::bytea[]))
10. 0.957 0.957 ↓ 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.956..0.957 rows=0 loops=1)

  • Index Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (read_for_users && ARRAY[(current_setting('nimble.user_id'::text))::bytea]))
11. 0.915 0.915 ↓ 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.915..0.915 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[]))
12. 0.935 0.935 ↓ 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.935..0.935 rows=0 loops=1)

  • Index Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (edit_for_users && ARRAY[(current_setting('nimble.user_id'::text))::bytea]))
13. 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.862..0.863 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[]))
14. 6,313.008 6,313.008 ↓ 0.0 0 1,052,168

Index Scan using contacts_values_pkey on contacts_values (cost=0.56..2.66 rows=1 width=48) (actual time=0.006..0.006 rows=0 loops=1,052,168)

  • Index Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (contact_id = contacts.id) AND (field_id = '\x000000000000000000000000'::bytea) AND (field_name = 'title'::text))
Planning time : 0.917 ms
Execution time : 10,008.914 ms