Re: JOIN on partitions is very slow
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. Thanks for your time. On Sun, Mar 22, 2020 at 11:38 PM Michael Lewis wrote: > Are you able to tweak the query or is that generated by an ORM? What > version of Postgres? Which configs have you changed from default? How many > partitions do you have? Is there an index on company name? > > Anytime I see distinct keyword, I expect it to be a performance bottleneck > and wonder about rewriting the query. Even just using group by can be much > faster because of how it gets executed. >
Re: Partition Pruning (Hash Partitions) Support for DELETEs in PostgreSQL 11 and 12
Thanks! On Mon, Mar 23, 2020 at 12:10 AM Justin Pryzby wrote: > On Sun, Mar 22, 2020 at 11:45:53PM -0400, Ronnie S wrote: > > Hello All, > > > > While doing some tests with hash partitioning behavior in PG11 and 12, I > > have found that PG11 is not performing partition pruning with DELETEs > > (explain analyze returned >2000 lines). I then ran the same test in PG12 > > and recreated the objects using the same DDL, and it worked > > > Is this a bug, somewhat related to MergeAppend? > > > https://github.com/postgres/postgres/commit/5220bb7533f9891b1e071da6461d5c387e8f7b09 > > > If it is, anyone know if we have a workaround for DELETEs to use hash > > partitions in PG11? > > I think due to this commit to pg12: > https://commitfest.postgresql.org/22/1778/ > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=428b260f87e8861ba8e58807b69d433db491c4f4 > ... > > https://www.postgresql.org/message-id/5c83dbca-12b5-1acf-0e85-58299e464a26%40lab.ntt.co.jp > > https://www.postgresql.org/message-id/4f049572-9440-3c99-afa1-f7ca7f38fe80%40lab.ntt.co.jp > > -- > Justin >
Re: JOIN on partitions is very slow
On Mon, Mar 23, 2020 at 1:40 AM daya airody 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' );
Re: JOIN on partitions is very slow
Michael Lewis schrieb am 23.03.2020 um 17:16: 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 As none of the columns of the joined table are used, most probably this should be re-written as an EXISTS condition. Then neither GROUP BY nor DISTINCT is needed.
