Adding LIMIT changes PostgreSQL plan from good to a bad one

Question

I have a query on a typical EAV schema:

SELECT contacts.id
FROM contacts
         LEFT OUTER JOIN (SELECT DISTINCT contacts_values.company_id AS company_id, contacts_values.id AS id
                          FROM contacts_values
                          WHERE contacts_values.field_id = '\x000000000000000000000000'
                            AND contacts_values.field_name = 'facebook'
                            AND nimble_contact_value_normalize(nimble_skip_long_values(contacts_values.value)) =
                                nimble_contact_value_normalize('http://www.facebook.com/jon.ferrara')) AS anon_1
                         ON anon_1.company_id = contacts.company_id AND anon_1.id = contacts.id
         LEFT OUTER JOIN (SELECT DISTINCT contacts_values.company_id AS company_id, contacts_values.id AS id
                          FROM contacts_values
                          WHERE contacts_values.field_id = '\x000000000000000000000000'
                            AND contacts_values.field_name = 'last_name'
                            AND nimble_contact_value_normalize(nimble_skip_long_values(contacts_values.value)) =
                                nimble_contact_value_normalize('Ferrara')) AS anon_2
                         ON anon_2.company_id = contacts.company_id AND anon_2.id = contacts.id
         JOIN contacts__aggregated AS contacts__aggregated_1
              ON contacts__aggregated_1.company_id = contacts.company_id AND
                 contacts__aggregated_1.contact_id = contacts.id AND contacts__aggregated_1.field_name = 'names'
WHERE contacts.company_id = '\x4c2118ad54397f271b000000'
  AND (anon_1.id IS NOT NULL OR anon_2.id IS NOT NULL)
ORDER BY contacts__aggregated_1.value ASC
LIMIT 30 OFFSET 0;

My problem is that the LIMIT clause in this query makes the planner choose a bad plan with nested loops: https://explain.depesz.com/s/Mute. Running the same query after SET ENABLE_NESTLOOP TO OFF I am getting a much more efficient plan: https://explain.depesz.com/s/b5kn. Removing the LIMIT from the query results in a similar plan: https://explain.depesz.com/s/wDqE.

One thing that concerns me is that the cost of the LIMIT node in the bad nested loop plan is just a fraction of the cost of its subnode. But for the better merge join plan LIMIT node has the same cost as its subnode. How could it be this way? And what can I do to make the planner pick up a better plan?

We are running PostgreSQL 10.10.

Answer 1

Tell PostgreSQL not to use the index by using

ORDER BY contacts__aggregated_1.value + 0 ASC

The problem is that PostgreSQL thinks it will be fastest if it scans contacts__aggregated in the ORDER BY order using the index, uses a nested loop to add rows from the other tables and filters out values that don't satisfy the conditions.

This is often a good strategy if not many values get filtered out.

It produces a bad plan, however, if

• there are fewer rows that satisfy the filter condition than PostgreSQL estimates
• the rows that satisfy the filter condition all have high value

One of these things must have happend. Look for high Rows removed by filter in the EXPLAIN (ANALYZE, BUFFERS) output.