9.3 optimizer - odd behaviour?
malc_p asked why IDS 9.3 used an INDEX PATH for a GROUP BY on policy_systype, the second column of a composite index, on a 4.5M-row table. Marco Greco answered that the estimated cost was lower than a sequential scan since the index is smaller than the data. david added that all needed columns were in the index (key-only), which is rare. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I've always held to the idea that index searches won't happen if you
are querying on a field which is not the top field in a composite
index.
e.g., our "contract" table (4.5M rows) has index (amongst others):
CREATE INDEX xyz ON contract(policy_number, policy_systype)
There are no other indexes using either of these fields.
So the query
SELECT policy_systype, count(*) FROM contract GROUP BY 1 ORDER BY 1
should be unindexed, yes?
But sqexplain shows:
QUERY:
------
select policy_systype, count(*) from contract
group by 1
order by 1
Estimated Cost: 11660067
Estimated # of Rows Returned: 10
Temporary Files Required For: Order By Group By
1) dba.contract: INDEX PATH <<<-----------!!!!!!!!!
(1) Index Keys: policy_number policy_systype (Key-Only) (Serial,
fragment
s: ALL)
What's causing this then?
malc_p@btinternet.com wrote:
> I've always held to the idea that index searches won't happen if you
> are querying on a field which is not the top field in a composite
> index.
> e.g., our "contract" table (4.5M rows) has index (amongst others):
> CREATE INDEX xyz ON contract(policy_number, policy_systype)>
> There are no other indexes using either of these fields.
>
> So the query
>
> SELECT policy_systype, count(*) FROM contract GROUP BY 1 ORDER BY 1>
> should be unindexed, yes?
>
> But sqexplain shows:
>
> QUERY:
>
> ------
>
> select policy_systype, count(*) from contract>
> group by 1
>
> order by 1
>
>
>
> Estimated Cost: 11660067
>
> Estimated # of Rows Returned: 10
>
> Temporary Files Required For: Order By Group By
>
>
>
> 1) dba.contract: INDEX PATH <<<-----------!!!!!!!!!
>
>
>
> (1) Index Keys: policy_number policy_systype (Key-Only) (Serial,
> fragment
> s: ALL)
>
>
> What's causing this then?
>
The fact that the cost is smaller than a sequential scan. And the cost is
presumably smaller because the index key is much smaller than the data and
thus you read way less pages through the index than through scanning the data.
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
Ah! The wonders of the optimizer never fail to amaze me. Hadn't considered that.
It is because everything (selected columns and columns in where/group/having) are all in the index. That is quite rare! David.