Understanding Optimizer
I am trying to understand the way in which the optimizer works.
Generally,
the optimizer is going to try and eliminate as many rows as possible
first,
and then move forward from there.
Below are a couple of plans from a query I've been working on trying to
understand the optimizer.
In QUERY(1), I disabled the index idx_4 and created idx_2. Looking at
the
query, I felt that there were more distinct values using idx_2. Also,
because of the way the columns were used in the where clause (=), I felt
is was
appropriate.
I ran update stats high for each of the columns referenced in the query.
Q: Any thoughts on why the optimizer is not considering idx_2? (In fact,
The optimizer choose to sequentially scan the large table first.)
Q: Anyone have tools that they use that helps you to predict what the
optimizer is
going to do?
Thanks.
Steve Romankiw
===============================================================================
About the tables:
pol: nrows = 147,185
----------------------
pol.prdctcd 112
pol.uwperid 287
pol.appid 77,869
pol indexes:
idx_1 appid
idx_2 appid
idx_2 prdctcd
idx_2 uwperid
idx_3 appid
idx_3 uwperid
idx_4 prdctcd
app: nrows = 258,120
----------------------
app.appid 251,574
recvdate 2,038
idx_10 appid
idx_11 uwperid
idx_12 recvdate
idx_12 apptype
idx_12 appstatus
idx_13 appid
idx_13 recvdate
group_prdct: nrows = 227
-------------------------
group_prdct.grouping 2
group_prdct.title 29
group_prdct.prodctcd 130
no indexes defined.
#-----
#----- After creating compound index on app(appid,recvdate)
#----- prdctcd index disabled.
#-----
QUERY(1):
------
SELECT POLPRM, POLTYPCD
FROM POL P, APP A, GROUP_PRDCT G
where P.APPID = A.APPID
AND P.UWPERID = 251
AND a.recvdate >= '01/01/1998'
AND a.recvdate <= '06/30/1998'
AND G.PRDCTCD = P.PRDCTCD
AND G.TITLE = 'FLEX'
AND G.GROUPING = 'PIF1'
AND POLSTATCD NOT IN
('VOID', 'CANCELED')
Estimated Cost: 39604
Estimated #----- of Rows Returned: 1
1) informix.p: SEQUENTIAL SCAN
Filters: (informix.p.uwperid = 251 AND informix.p.polstatcd NOT IN
('VOID' , 'CANCELED' ))
2) informix.g: SEQUENTIAL SCAN
Filters: (informix.g.title = 'FLEX' AND informix.g.grouping = 'PIF1'
)
DYNAMIC HASH JOIN
Dynamic Hash Filters: informix.p.prdctcd = informix.g.prdctcd
3) informix.a: INDEX PATH
(1) Index Keys: appid recvdate (Key-Only)
Lower Index Filter: (informix.a.recvdate >= 01/01/1998 AND
informix.a.appid = informix.p.appid )
Upper Index Filter: informix.a.recvdate <= 06/30/1998
#-----
#----- Enabled prdctcd index. This removed Dyanmic hash join
#-----
QUERY(2):
------
SELECT POLPRM, POLTYPCD
FROM POL P, APP A, GROUP_PRDCT G
where P.APPID = A.APPID
AND P.UWPERID = 251
AND a.recvdate >= '01/01/1998'
AND a.recvdate <= '06/30/1998'
AND G.PRDCTCD = P.PRDCTCD
AND G.TITLE = 'FLEX'
AND G.GROUPING = 'PIF1'
AND POLSTATCD NOT IN
('VOID', 'CANCELED')
Estimated Cost: 21
Estimated #----- of Rows Returned: 1
1) informix.g: SEQUENTIAL SCAN
Filters: (informix.g.title = 'FLEX' AND informix.g.grouping = 'PIF1'
)
2) informix.p: INDEX PATH
Filters: (informix.p.uwperid = 251 AND informix.p.polstatcd NOT IN
('VOID' , 'CANCELED' ))
(1) Index Keys: prdctcd
Lower Index Filter: informix.p.prdctcd = informix.g.prdctcd
3) informix.a: INDEX PATH
(1) Index Keys: appid recvdate (Key-Only)
Lower Index Filter: (informix.a.recvdate >= 01/01/1998 AND
informix.a.appid = informix.p.appid )
Upper Index Filter: informix.a.recvdate <= 06/30/1998