Published on InterSystems Developer Community (https://community.intersystems.com)

Home > Query Plan Error or Correct Estimation

Discussion
Neerav Verma · Jan 16, 2020

Query Plan Error or Correct Estimation

Hi All,

We have few queries which are simple selects . For simplicity let's say there is a query that joins two tables and gets few columns and both tables have no indexes.

Select Tab1.Field1, Tab2.Field2
From Table1 Tab1
Join Table2 Tab2
On Tab2.FK = Tab1.PK

When we do query plan for this it shows approx 6 million,  however if we make a simple adjustment to the query

Select Tab1.Field1, Tab2.Field2
From Table1 Tab1
Join Table2 Tab2
On Tab2.FK = Tab1.PK
WHERE Tab1.Id > 0 (Which will always be the case)

The query plan comes down to few thousands. So approx 99% improvement.

Is this approximation correct or is it a bug in cache??

#Performance #SQL #Caché

Source URL:https://community.intersystems.com/post/query-plan-error-or-correct-estimation