1

I am running PostgreSQL 10.9 and stuck with a strange problem. My schema is a typical EAV with two tables: contacts and contacts_values. There are two relevant indexes:

create unique index contacts_pkey
    on contacts (company_id, id);
create index tmp_ix_company_id_field_id
    on contacts_values (company_id, field_id, field_name, id);

I need to select all contacts that either have or have not any value some attribute. There are two queries what are only different in IS NULL vs. IS NOT NULL part:

SELECT contacts.id
                FROM contacts
                         LEFT JOIN contacts_values AS contacts_values_1
                                   ON contacts_values_1.company_id = contacts.company_id AND
                                      contacts_values_1.id = contacts.id AND
                                      contacts_values_1.field_id = '\x000000000000000000000000' AND
                                      contacts_values_1.field_name = 'email_work'
                WHERE contacts.company_id = '\x4c2118ad54397f271b000000'
                  AND contacts_values_1.id IS NULL;

SELECT contacts.id
                FROM contacts
                         LEFT JOIN contacts_values AS contacts_values_1
                                   ON contacts_values_1.company_id = contacts.company_id AND
                                      contacts_values_1.id = contacts.id AND
                                      contacts_values_1.field_id = '\x000000000000000000000000' AND
                                      contacts_values_1.field_name = 'email_work'
                WHERE contacts.company_id = '\x4c2118ad54397f271b000000'
                  AND contacts_values_1.id IS NOT NULL;

However, their plans are quite different: https://explain.depesz.com/s/9zLQ for IS NOT NULL and https://explain.depesz.com/s/tbRW for IS NULL. The main difference is that the first plan uses tmp_ix_company_id_field_id both to select and sort rows and the second plan uses the same index to select rows but sorts them as the separate step.

I tried to rewrite these queries using EXISTS/NOT EXISTS but the plans stay the same.

Is it possible to make use of the index to sort rows in both situations? Or, looking wider, is it possible to build a more efficient query for my problem?

5
  • Any chance you can provide a self contained test case, including some kind of data generator? I am not able to generate data that reproduces this plan, as it keeps choosing other plans instead.
    – jjanes
    Commented Sep 22, 2019 at 16:34
  • What happens if you set enable_material TO off before the 2nd plan?
    – jjanes
    Commented Sep 22, 2019 at 16:35
  • 1
    Can you increase work_mem to at least 50MB? If you routinely run queries of this size, you should dedicate some memory to them. It might get rid of the sort, or might at least make it faster.
    – jjanes
    Commented Sep 22, 2019 at 16:38
  • After I increased work_mem to 32MB external disk sorting for IS NOT NULL has gone: explain.depesz.com/s/ckTC. The query performance is acceptable now, thank you for your idea!
    – rmihael
    Commented Sep 23, 2019 at 8:28
  • However, I am still curious about the necessity of that step. The plan stays the same (explain.depesz.com/s/ckTC) regardless of enable_material setting. What prevents the planner from using the index to avoid sorting?
    – rmihael
    Commented Sep 23, 2019 at 8:32

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.