Nasty bug in Informix optimizer...
Hey Informix,
Did it ever occur to you to evaluate a function before optimization?
Here's the problem.
I have a simple query on a table that is fragmented by month of year. I
have a field lets call inputMonth....
So I say
Select foo....
From bar
Where inputMonth = MONTH(TODAY) -1;
Now the problem is that I scan every fragment, instead of only last
month's fragment.
If I said
Where inputMonth = x
where x is some litteral passed in, the program scans the proper
fragment.
Now if this seems confusing, look at it this way.
Whats the difference between the following code fragments
for (i=0; i < foo(r); i++) {
; do something stupid;
}
and
j = foo(r);
for(i=0; i <j; i++){
;do something stupid;
}
foo() is some function, and for the sake of arguments, i,j,r are all
integers.
Ok, this is actually an interview question I ask people as part of a
technical interview.
Seems simple right?
You'd be surprised at the answers I get.....
-Mikey
Hi Mike,
The 7.31 optimizer does appear to eliminate fragments correctly. Running
the SQL statements below ...
create table t (c int)
fragment by expression
(c = 10) in dbspace1,
(c = 11) in dbspace2;
insert into t values (10);
insert into t values (11);
update statistics high for table t;
select partnum
from sysmaster:systabnames
where dbsname = "dbs"
and tabname = "t"
into temp tmp;
select p.partnum, p.seqscans
from sysmaster:sysptprof p, tmp t
where p.partnum = t.partnum;
select * from t where c = month(today) - 1;
select p.partnum, p.seqscans
from sysmaster:sysptprof p, tmp t
where p.partnum = t.partnum;
... produces the following output:
Table created.
1 row(s) inserted.
1 row(s) inserted.
Statistics updated.
2 row(s) retrieved into temp table.
partnum seqscans
2097216 1
3145765 1
2 row(s) retrieved.
c
10
1 row(s) retrieved.
partnum seqscans
2097216 2
3145765 1
2 row(s) retrieved.
Database closed.
The 'seqscans' column shows the number of sequential scans on the
partitions (fragments) associated with 't' before and after the query. As
you can see, only the first fragment is scanned.
Also, in this version the sqexplain.out file includes the following
message:
QUERY:
------
select * from t where c = month(today) - 1
Estimated Cost: 3
Estimated # of Rows Returned: 1
1) erikv.t: SEQUENTIAL SCAN (Serial, fragments: ALL)
(fragments might be eliminated at runtime because filter contains
runtime constants)
Filters: erikv.t.c = MONTH (TODAY ) - 1
Note that I verified this against my private source tree, so I can't say
what exact IDS 7.31 version rectifies the behaviour of your current version
- I would guess any version starting with 7.31.UC2 will work.
Our optimizer is really smart ;-)
Cheers, Erik
Mike Segel wrote:
> Hey Informix,
>
> Did it ever occur to you to evaluate a function before optimization?
>
> Here's the problem.
>
> I have a simple query on a table that is fragmented by month of year. I
> have a field lets call inputMonth....
>
> So I say
>
> Select foo....
> From bar
> Where inputMonth = MONTH(TODAY) -1;>
> Now the problem is that I scan every fragment, instead of only last
> month's fragment.
>
> If I said
> Where inputMonth = x
>
> where x is some litteral passed in, the program scans the proper
> fragment.
>
> Now if this seems confusing, look at it this way.
>
> Whats the difference between the following code fragments
>
> for (i=0; i < foo(r); i++) {
> ; do something stupid;
> }
>
> and
> j = foo(r);
> for(i=0; i <j; i++){
> ;do something stupid;
> }
>
> foo() is some function, and for the sake of arguments, i,j,r are all
> integers.
>
> Ok, this is actually an interview question I ask people as part of a
> technical interview.
>
> Seems simple right?
> You'd be surprised at the answers I get.....
>
> -Mikey
Erik,
Not on HPUX 10.2 7.31 on a T series box. :-(
-Mike
Erik van Veen wrote:
> Hi Mike,
>
> The 7.31 optimizer does appear to eliminate fragments correctly. Running
> the SQL statements below ...
>
> create table t (c int)
> fragment by expression
> (c = 10) in dbspace1,
> (c = 11) in dbspace2;>
> insert into t values (10);
> insert into t values (11);>
> update statistics high for table t;>
> select partnum
> from sysmaster:systabnames
> where dbsname = "dbs"
> and tabname = "t"
> into temp tmp;>
> select p.partnum, p.seqscans
> from sysmaster:sysptprof p, tmp t
> where p.partnum = t.partnum;>
> select * from t where c = month(today) - 1;>
> select p.partnum, p.seqscans
> from sysmaster:sysptprof p, tmp t
> where p.partnum = t.partnum;>
> ... produces the following output:
>
> Table created.
>
> 1 row(s) inserted.
>
> 1 row(s) inserted.
>
> Statistics updated.
>
> 2 row(s) retrieved into temp table.
>
> partnum seqscans
>
> 2097216 1
> 3145765 1
>
> 2 row(s) retrieved.
>
> c
>
> 10
>
> 1 row(s) retrieved.
>
> partnum seqscans
>
> 2097216 2
> 3145765 1
>
> 2 row(s) retrieved.
>
> Database closed.
>
> The 'seqscans' column shows the number of sequential scans on the
> partitions (fragments) associated with 't' before and after the query. As
> you can see, only the first fragment is scanned.
> Also, in this version the sqexplain.out file includes the following
> message:
>
> QUERY:
> ------
> select * from t where c = month(today) - 1>
> Estimated Cost: 3
> Estimated # of Rows Returned: 1
>
> 1) erikv.t: SEQUENTIAL SCAN (Serial, fragments: ALL)
> (fragments might be eliminated at runtime because filter contains
> runtime constants)
>
> Filters: erikv.t.c = MONTH (TODAY ) - 1
>
> Note that I verified this against my private source tree, so I can't say
> what exact IDS 7.31 version rectifies the behaviour of your current version
>
> - I would guess any version starting with 7.31.UC2 will work.
>
> Our optimizer is really smart ;-)
>
> Cheers, Erik
>
> Mike Segel wrote:
>
> > Hey Informix,
> >
> > Did it ever occur to you to evaluate a function before optimization?
> >
> > Here's the problem.
> >
> > I have a simple query on a table that is fragmented by month of year. I
> > have a field lets call inputMonth....
> >
> > So I say
> >
> > Select foo....
> > From bar
> > Where inputMonth = MONTH(TODAY) -1;> >
> > Now the problem is that I scan every fragment, instead of only last
> > month's fragment.
> >
> > If I said
> > Where inputMonth = x
> >
> > where x is some litteral passed in, the program scans the proper
> > fragment.
> >
> > Now if this seems confusing, look at it this way.
> >
> > Whats the difference between the following code fragments
> >
> > for (i=0; i < foo(r); i++) {
> > ; do something stupid;
> > }
> >
> > and
> > j = foo(r);
> > for(i=0; i <j; i++){
> > ;do something stupid;
> > }
> >
> > foo() is some function, and for the sake of arguments, i,j,r are all
> > integers.
> >
> > Ok, this is actually an interview question I ask people as part of a
> > technical interview.
> >
> > Seems simple right?
> > You'd be surprised at the answers I get.....
> >
> > -Mikey
Hi Mike,
hmm, I just verified this using IDS 7.30.UC8 on a HP-UX 11.00 box - and it does
the business for me. Can you forward me the output of the SQL statements embedded
in my earlier reply and let me know what exact IDS version you're using ?
Cheers, Erik
Mike Segel wrote:
> Erik,
> Not on HPUX 10.2 7.31 on a T series box. :-(
>
> -Mike
>
> Erik van Veen wrote:
>
> > Hi Mike,
> >
> > The 7.31 optimizer does appear to eliminate fragments correctly. Running
> > the SQL statements below ...
> >
> > create table t (c int)
> > fragment by expression
> > (c = 10) in dbspace1,
> > (c = 11) in dbspace2;> >
> > insert into t values (10);
> > insert into t values (11);> >
> > update statistics high for table t;> >
> > select partnum
> > from sysmaster:systabnames
> > where dbsname = "dbs"
> > and tabname = "t"
> > into temp tmp;> >
> > select p.partnum, p.seqscans
> > from sysmaster:sysptprof p, tmp t
> > where p.partnum = t.partnum;> >
> > select * from t where c = month(today) - 1;> >
> > select p.partnum, p.seqscans
> > from sysmaster:sysptprof p, tmp t
> > where p.partnum = t.partnum;> >
> > ... produces the following output:
> >
> > Table created.
> >
> > 1 row(s) inserted.
> >
> > 1 row(s) inserted.
> >
> > Statistics updated.
> >
> > 2 row(s) retrieved into temp table.
> >
> > partnum seqscans
> >
> > 2097216 1
> > 3145765 1
> >
> > 2 row(s) retrieved.
> >
> > c
> >
> > 10
> >
> > 1 row(s) retrieved.
> >
> > partnum seqscans
> >
> > 2097216 2
> > 3145765 1
> >
> > 2 row(s) retrieved.
> >
> > Database closed.
> >
> > The 'seqscans' column shows the number of sequential scans on the
> > partitions (fragments) associated with 't' before and after the query. As
> > you can see, only the first fragment is scanned.
> > Also, in this version the sqexplain.out file includes the following
> > message:
> >
> > QUERY:
> > ------
> > select * from t where c = month(today) - 1> >
> > Estimated Cost: 3
> > Estimated # of Rows Returned: 1
> >
> > 1) erikv.t: SEQUENTIAL SCAN (Serial, fragments: ALL)
> > (fragments might be eliminated at runtime because filter contains
> > runtime constants)
> >
> > Filters: erikv.t.c = MONTH (TODAY ) - 1
> >
> > Note that I verified this against my private source tree, so I can't say
> > what exact IDS 7.31 version rectifies the behaviour of your current version
> >
> > - I would guess any version starting with 7.31.UC2 will work.
> >
> > Our optimizer is really smart ;-)
> >
> > Cheers, Erik
> >
> > Mike Segel wrote:
> >
> > > Hey Informix,
> > >
> > > Did it ever occur to you to evaluate a function before optimization?
> > >
> > > Here's the problem.
> > >
> > > I have a simple query on a table that is fragmented by month of year. I
> > > have a field lets call inputMonth....
> > >
> > > So I say
> > >
> > > Select foo....
> > > From bar
> > > Where inputMonth = MONTH(TODAY) -1;> > >
> > > Now the problem is that I scan every fragment, instead of only last
> > > month's fragment.
> > >
> > > If I said
> > > Where inputMonth = x
> > >
> > > where x is some litteral passed in, the program scans the proper
> > > fragment.
> > >
> > > Now if this seems confusing, look at it this way.
> > >
> > > Whats the difference between the following code fragments
> > >
> > > for (i=0; i < foo(r); i++) {
> > > ; do something stupid;
> > > }
> > >
> > > and
> > > j = foo(r);
> > > for(i=0; i <j; i++){
> > > ;do something stupid;
> > > }
> > >
> > > foo() is some function, and for the sake of arguments, i,j,r are all
> > > integers.
> > >
> > > Ok, this is actually an interview question I ask people as part of a
> > > technical interview.
> > >
> > > Seems simple right?
> > > You'd be surprised at the answers I get.....
> > >
> > > -Mikey