Thank you Tom for initial thoughts! I tried query with function dependency statistics but it did not help. I don't see we can create such statistics on table join.
Regards, Virendra -----Original Message----- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, December 17, 2018 12:02 PM To: Kumar, Virendra Cc: pgsql-general@lists.postgresql.org Subject: Re: NL Join vs Merge Join - 5 hours vs 2 seconds "Kumar, Virendra" <virendra.ku...@guycarp.com> writes: > We are using Postgers-XL based on PostgreSQL 10.5 on RHEL7. We have an > extreme case of one query which running in 4 hour 45 mins and 33 seconds with > Nested Loops Join vs 2.5 seconds when we disable enable_nestloop. The query > is given below. I would be happy to share more information if required. It looks like what's burning you is bad estimates at these joins: > -> Nested Loop > (cost=33681.00..71431.33 rows=1 width=348) (actual time=359.006..926.303 > rows=37207 loops=3) > Join Filter: (se.account_id = > a.account_id) > -> Hash Join > (cost=33680.71..71298.94 rows=395 width=300) (actual time=358.953..656.923 > rows=36828 loops=3) > Hash Cond: ((se.account_id > = sh.account_id) AND (se.site_id = sh.site_id)) ... > -> Index Scan using > account_p1000013_account_id_idx on account_p1000013 a (cost=0.29..0.32 > rows=1 width=52) (actual time=0.003..0.005 rows=1 loops=110485) > Index Cond: (account_id = > sh.account_id) > Filter: (portfolio_id = > 1000013) I'm guessing that account_id, site_id, and portfolio_id are all highly correlated, but the planner doesn't know that and thinks the additional conditions will remove way more rows than they actually do. In PG10 and later, you can probably fix that by creating functional-dependency statistics on those pairs of columns. regards, tom lane ________________________________ This message is intended only for the use of the addressee and may contain information that is PRIVILEGED AND CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify the sender immediately. Thank you.
