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.

• rmihael: Thank you again. Can you advise where to look for directions on how to tune a statistics collector to avoid such problems in the future? If that happened with that query it can also affect some other.
• Laurenz Albe: That usually has nothing to do with statistics, but with correlation. Try to ANALYZE all involved tables - if that fixes the problem, bad statistics were the cause.
• rmihael: That was the first thing I tried. Also, I did SET default_statistics_target TO 1000; and created extended correlation statistic on contact_values: CREATE STATISTICS contacts_values_company_id_field (dependencies) ON company_id, field_id, field_name FROM contacts_values. That hasn't changed the plan at all.
• Laurenz Albe: Right. Then that was not the problem.
• rmihael: One strange thing that just grabbed my eye is that that planner chooses to first join contacts and contacts_aggregated, both over a million rows. And only after it applies filtering based on contacts_values, giving ~80 rows as the result. Joining and filtering contacts and contacts_values first and then applying sorting by contacts__aggregated seems to be a better plan.