explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OO1K

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 8,988.604 ↑ 1.0 30 1

Limit (cost=57,556.89..57,556.96 rows=30 width=27) (actual time=8,988.599..8,988.604 rows=30 loops=1)

2. 693.108 8,988.599 ↑ 382.5 30 1

Sort (cost=57,556.89..57,585.58 rows=11,475 width=27) (actual time=8,988.597..8,988.599 rows=30 loops=1)

  • Sort Key: contacts__aggregated_1.value, contacts.id
  • Sort Method: top-N heapsort Memory: 28kB
3. 103.317 8,295.491 ↓ 91.6 1,050,640 1

Result (cost=2,790.71..57,217.98 rows=11,475 width=27) (actual time=265.608..8,295.491 rows=1,050,640 loops=1)

  • One-Time Filter: ((current_setting('nimble.company_id'::text))::bytea = '\x4c2118ad54397f271b000000'::bytea)
4. 134.795 8,192.174 ↓ 91.6 1,050,640 1

Nested Loop (cost=2,790.71..57,217.98 rows=11,475 width=27) (actual time=265.605..8,192.174 rows=1,050,640 loops=1)

5. 1,499.059 1,753.539 ↓ 80.3 1,050,640 1

Bitmap Heap Scan on contacts (cost=2,790.28..28,767.49 rows=13,083 width=26) (actual time=265.564..1,753.539 rows=1,050,640 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[])) AND (NOT (hashed SubPlan 1)))
  • Rows Removed by Filter: 1,528
  • Heap Blocks: exact=49,782
6. 0.004 224.865 ↓ 0.0 0 1

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

7. 0.014 0.014 ↓ 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.014..0.014 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))
8. 219.926 219.926 ↓ 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=219.926..219.926 rows=1,053,382 loops=1)

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

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

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

Bitmap Index Scan on ix_company_rls_permissions (cost=0.00..137.54 rows=5,283 width=0) (actual time=1.020..1.020 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[]))
11. 1.150 1.150 ↓ 0.0 0 1

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

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

Bitmap Index Scan on ix_company_rls_permissions (cost=0.00..137.54 rows=5,283 width=0) (actual time=1.352..1.352 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[]))
13.          

SubPlan (for Bitmap Heap Scan)

14. 2.550 29.615 ↑ 1.2 1,528 1

Bitmap Heap Scan on contacts__aggregated (cost=186.60..2,076.68 rows=1,780 width=13) (actual time=27.260..29.615 rows=1,528 loops=1)

  • Recheck Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (field_name = 'names'::text) AND (value__tsv @@ '''jon'''::tsquery))
  • Heap Blocks: exact=1,335
15. 27.065 27.065 ↑ 1.2 1,528 1

Bitmap Index Scan on ft_ix_contacts__aggregated_value__tsv (cost=0.00..186.15 rows=1,780 width=0) (actual time=27.064..27.065 rows=1,528 loops=1)

  • Index Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (field_name = 'names'::text) AND (value__tsv @@ '''jon'''::tsquery))
16. 6,303.840 6,303.840 ↑ 1.0 1 1,050,640

Index Scan using ix_contacts__aggregated_company_id_field_name_contact_id on contacts__aggregated contacts__aggregated_1 (cost=0.43..2.16 rows=1 width=40) (actual time=0.006..0.006 rows=1 loops=1,050,640)

  • Index Cond: ((company_id = '\x4c2118ad54397f271b000000'::bytea) AND (field_name = 'names'::text) AND (contact_id = contacts.id))
Planning time : 1.182 ms
Execution time : 8,989.622 ms