Re: JOIN on partitions is very slow

On Mon, Mar 23, 2020 at 1:40 AM daya airody <daya(dot)airody(at)gmail(dot)com> wrote:

> Yes. I can tweak the query. Version of postgres is 9.5.15. I have about 20
> partitions for company_sale_account table.
> I do have an index on company name.
>
> I need to use DISTINCT as i need to remove the duplicates.
>

DISTINCT is a sign of improper joins most of the time in my experience.
Often, just changing to group by is faster

SELECT cpsa1.*
FROM company_sale_account cpsa1
JOIN company_sale_account cpsa2 ON cpsa1.sale_account_id =
cpsa2.sale_account_id
WHERE cpsa1.company_name = 'company_a'
AND cpsa2.company_name = 'company_b'
GROUP BY cpsa1.id; --assuming primary key exists, and I forget if the
feature that allows only naming primary key in group by might have been
introduced with 9.6

It should be noted that 9.5 is about 1 year from being EOL'd so it would be
prudent to update to v11 or 12 when possible.

How does the below query perform? By the way, "top posting" (replying with
all previous email thread below your reply) is discouraged on these forums.
It makes the reviewing archived posts more cumbersome. Instead, please
reply with only your message and copying the relevant parts of prior
conversation that you are responding to.

SELECT cpsa1.*
FROM company_sale_account cpsa1
WHERE cpsa1.company_name = 'company_a' AND EXISTS(SELECT FROM
company_sale_account cpsa2 WHER cpsa1.sale_account_id =
cpsa2.sale_account_id AND cpsa2.company_name = 'company_b' );