Re: Adding LIMIT changes PostgreSQL plan from good to a bad one

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: Adding LIMIT changes PostgreSQL plan from good to a bad one
Дата
Msg-id CAHOFxGo8Mh=jw2zsnTfn6EDS-eP9dGotF3+k1LL6K1FkS-=FPA@mail.gmail.com
обсуждение исходный текст
Ответ на Adding LIMIT changes PostgreSQL plan from good to a bad one  (Michael Korbakov <rmihael@gmail.com>)
Ответы Re: Adding LIMIT changes PostgreSQL plan from good to a bad one
Список pgsql-general
Дерево обсуждения
I try to avoid DISTINCT and use GROUP BY when feasible, as well as avoiding OR condition. If you combined anon1 and anon2 with UNION ALL, and did (inner) join instead of left, or even moved all of that to EXISTS, perhaps that gives you better consistent performance. Something like this-


SELECT contacts.id
FROM contacts
    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 EXISTS (
         SELECT
                     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'))
                       AND contacts_values.company_id = contacts.company_id AND contacts_values.id = contacts.id
         UNION ALL
         SELECT
                     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')
                       AND contacts_values.company_id = contacts.company_id AND contacts_values.id = contacts.id
)

ORDER BY contacts__aggregated_1.value ASC
LIMIT 30 OFFSET 0;

В списке pgsql-general по дате отправления: