explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UIog

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 6,674.470 ↓ 6.0 6 1

Limit (cost=3,253,504.02..3,253,504.02 rows=1 width=29) (actual time=6,674.467..6,674.470 rows=6 loops=1)

  • Output: contacts.id, contacts__aggregated_1.value
2. 0.080 6,674.467 ↓ 6.0 6 1

Sort (cost=3,253,504.02..3,253,504.02 rows=1 width=29) (actual time=6,674.466..6,674.467 rows=6 loops=1)

  • Output: contacts.id, contacts__aggregated_1.value
  • Sort Key: contacts__aggregated_1.value
  • Sort Method: top-N heapsort Memory: 25kB
3. 0.012 6,674.387 ↓ 114.0 114 1

Result (cost=3,253,141.08..3,253,504.01 rows=1 width=29) (actual time=6,672.750..6,674.387 rows=114 loops=1)

  • Output: contacts.id, contacts__aggregated_1.value
  • One-Time Filter: ((current_setting('nimble.company_id'::text))::bytea = '\x4c2118ad54397f271b000000'::bytea)
4. 0.069 6,674.375 ↓ 114.0 114 1

Nested Loop (cost=3,253,141.08..3,253,504.01 rows=1 width=29) (actual time=6,672.745..6,674.375 rows=114 loops=1)

  • Output: contacts.id, contacts__aggregated_1.value
  • Inner Unique: true
  • Join Filter: (contacts.id = contacts__aggregated_1.contact_id)
5. 0.098 6,673.508 ↓ 38.0 114 1

Nested Loop (cost=3,253,140.50..3,253,495.29 rows=3 width=39) (actual time=6,672.724..6,673.508 rows=114 loops=1)

  • Output: contacts.id, contacts.company_id, contacts_values_1.id
  • Inner Unique: true
6. 0.220 6,672.726 ↑ 1.4 114 1

HashAggregate (cost=3,253,139.94..3,253,141.57 rows=163 width=13) (actual time=6,672.704..6,672.726 rows=114 loops=1)

  • Output: contacts_values_1.id
  • Group Key: contacts_values_1.id
7. 2,007.357 6,672.506 ↑ 1.4 114 1

Hash Join (cost=1,627,980.53..3,253,139.53 rows=163 width=13) (actual time=4,039.208..6,672.506 rows=114 loops=1)

  • Output: contacts_values_1.id
  • Hash Cond: (nimble_contact_value_normalize(CASE WHEN (length(contacts_values_1.value) < 1024) THEN contacts_values_1.value ELSE NULL::text END) = encode(contacts_values_2.id, 'hex'::text))
8. 686.331 1,502.934 ↑ 2.8 359,284 1

Bitmap Heap Scan on public.contacts_values contacts_values_1 (cost=14,242.31..1,504,152.95 rows=1,020,730 width=50) (actual time=876.537..1,502.934 rows=359,284 loops=1)

  • Output: contacts_values_1.company_id, contacts_values_1.value, contacts_values_1.id
  • Recheck Cond: ((contacts_values_1.company_id = '\x4c2118ad54397f271b000000'::bytea) AND (contacts_values_1.field_name = 'parent_company'::text) AND (contacts_values_1.field_id = '\x000000000000000000000000'::bytea))
  • Heap Blocks: exact=229004
9. 816.603 816.603 ↑ 2.8 359,292 1

Bitmap Index Scan on ft_ix_contacts_values (cost=0.00..13,987.13 rows=1,020,730 width=0) (actual time=816.603..816.603 rows=359,292 loops=1)

  • Index Cond: ((contacts_values_1.company_id = '\x4c2118ad54397f271b000000'::bytea) AND (contacts_values_1.field_name = 'parent_company'::text) AND (contacts_values_1.field_id = '\x000000000000000000000000'::bytea))
10. 0.115 3,162.215 ↓ 4.7 52 1

Hash (cost=1,613,738.09..1,613,738.09 rows=11 width=26) (actual time=3,162.215..3,162.215 rows=52 loops=1)

  • Output: contacts_values_2.company_id, contacts_values_2.id
  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
11. 2,479.094 3,162.100 ↓ 4.7 52 1

Bitmap Heap Scan on public.contacts_values contacts_values_2 (cost=12,594.53..1,613,738.09 rows=11 width=26) (actual time=732.024..3,162.100 rows=52 loops=1)

  • Output: contacts_values_2.company_id, contacts_values_2.id
  • Recheck Cond: ((contacts_values_2.company_id = '\x4c2118ad54397f271b000000'::bytea) AND (contacts_values_2.field_name = 'company_name'::text) AND (contacts_values_2.field_id = '\x000000000000000000000000'::bytea))
  • Filter: (nimble_contact_value_normalize(CASE WHEN (length(contacts_values_2.value) < 1024) THEN contacts_values_2.value ELSE NULL::text END) = 'nimble'::text)
  • Rows Removed by Filter: 325929
  • Heap Blocks: exact=206027
12. 683.006 683.006 ↑ 2.8 325,985 1

Bitmap Index Scan on ft_ix_contacts_values (cost=0.00..12,594.53 rows=915,402 width=0) (actual time=683.006..683.006 rows=325,985 loops=1)

  • Index Cond: ((contacts_values_2.company_id = '\x4c2118ad54397f271b000000'::bytea) AND (contacts_values_2.field_name = 'company_name'::text) AND (contacts_values_2.field_id = '\x000000000000000000000000'::bytea))
13. 0.684 0.684 ↑ 1.0 1 114

Index Only Scan using ix_company_id_contact_type on public.contacts (cost=0.56..2.17 rows=1 width=26) (actual time=0.006..0.006 rows=1 loops=114)

  • Output: contacts.company_id, contacts.contact_type, contacts.id
  • Index Cond: ((contacts.company_id = '\x4c2118ad54397f271b000000'::bytea) AND (contacts.contact_type = 'person'::text) AND (contacts.id = contacts_values_1.id))
  • Heap Fetches: 10
14. 0.798 0.798 ↑ 1.0 1 114

Index Scan using ix_contacts__aggregated_company_id_field_name_contact_id on public.contacts__aggregated contacts__aggregated_1 (cost=0.56..2.89 rows=1 width=42) (actual time=0.007..0.007 rows=1 loops=114)

  • Output: contacts__aggregated_1.company_id, contacts__aggregated_1.contact_id, contacts__aggregated_1.field_name, contacts__aggregated_1.value
  • Index Cond: ((contacts__aggregated_1.company_id = '\x4c2118ad54397f271b000000'::bytea) AND (contacts__aggregated_1.field_name = 'names'::text) AND (contacts__aggregated_1.contact_id = contacts_values_1.id))
Planning time : 1.197 ms
Execution time : 6,681.186 ms