On Mon, Mar 23, 2020 at 1:40 AM daya airody <daya.airody@xxxxxxxxx> 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
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.
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' );
