Performance tuning select query with OR condition in JOIN

Question

I have large SELECT query to find out the product related details.

Some part of the query is taking too much time due to OR condition in the JOIN.

Sample code:

Existing query:

--Time Taken: 00:00:04
--Records   : 179384
SELECT * FROM ProductMaster pm
WHERE (pm.prdname is null or (pm.prdname = 'EV' AND pm.prdtype = 'EType'));

As we can see here the condition (pm.prdname is null or (pm.prdname = 'EV' AND pm.prdtype = 'EType')) is using OR, due which I'm getting 1min of time to execute main query for 4500 records.

If I modify it with the following it execute within second but no result displayed.

--Time Taken: 00:00:01
--Records   : 0
SELECT * FROM ProductMaster pm
WHERE pm.prdname IN (NULL,'EV') AND pm.prdtype = 'EType';

I have also tried with this approach but it took 00:01:45 time and also records are not matching with the existing query.

--Time Taken: 00:01:45
--Records   : 4,525,052
SELECT * FROM ProductMaster pm
WHERE (pm.prdname IN (NULL,'EV') OR pm.prdtype = 'EType');

Note: The JOIN in main SELECT statement looks like this:

left outer join ProductMaster pm on Pcode=pm.Pcode and SaleDate=pm.SaleDate and prdscode=pm.prdscode
AND (pm.prdname is null or (pm.prdname = 'EV' AND pm.prdtype = 'EType'))

Could you please share the results from EXPLAIN(ANALYZE, VERBOSE, BUFFERS) for this statement, the DDL for the tables and the DDL for the available indexes? All in plain text as an update to your question — Frank Heikens, Apr 26 '23 at 07:10
@FrankHeikens, Added execution plan. Table `prd_dbo.availabilitycontrol` is same as given in example `ProductMaster`. — MAK, Apr 26 '23 at 07:20
Millions of rows are first collected and then removed. Your indexes don't (completely) cover the conditions in your query. Worst part is the Index Only Scan that takes over 70% of the time needed for this query. work_mem is also too small for the current query, it has to sort on disk. But that's a minor problem compared to the unknown indexes. — Frank Heikens, Apr 26 '23 at 07:33
Your plan does not match your query. Decide what it is you want to optimize, and show that query, and the real plan for *that* query, not some other query. If you have simplified the query (while retaining the performance problem), that is fine but then show us that simplified query and the real plan for it. — jjanes, Apr 26 '23 at 15:02
If adding the correct indexes doesn't solve this issue, then often splitting an `OR` into 2 queries with `UNION ALL` will improve performance. — Dale K, Jun 07 '23 at 04:52

Stefanov.sm · Answer 1 · 2023-04-26T10:01:56.653

I would try something like this, removing the filtering expression from the ON clause.

left outer join
(
 select * from ProductMaster
 where prdname is null or (prdname = 'EV' and prdtype = 'EType')
) pm
using (Pcode, SaleDate, prdscode);

or

left outer join ProductMaster pm using (Pcode, SaleDate, prdscode)
where pm.prdname is null or (pm.prdname = 'EV' and pm.prdtype = 'EType')

BTW your first example prdname is null or (prdname = 'EV' AND prdtype = 'EType') is not equivalent to the second and third ones.

Performance tuning select query with OR condition in JOIN

1 Answers1