SEQUENTIAL SCANS ~ Answered: amber (solid confidence) — A thorough community diagnosis explains why the cost-based optimizer prefers a sequential scan once an OR spans two indexed columns in a join, and an empirically-verified UNION/IN rewrite (or AVOID_FULL directive) works around it in a reproducible test case; however the asker says the plain UNION rewrite doesn't fit his real expanded query, a later message shows the forced-index workaround still doesn't apply the join filter at the index level as expected, and no final confirmation is given.

Advisory only.

Version IDS 10 I am running a query something like this: table A (column s, column t, column u, column v) table A has individual index on column s. table A has another individual index on column t. table A has another individual index on column u. Update stats have run as high on these columns table B (column w, column x) table B has individual index on column w. table B has another individual index on column x. Update stats have run as high on these columns select * from A, B where A.s = 1200 and B.w = A.t; Above query correctly takes INDEX PATH. Now I change the query to: select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u); set explain shows that it did SEQUENTIAL SCAN on table B. It's perplexing why would optimizer behave like this. It should be taking index path for the second query too.

mohitanchlia@gmail.com wrote: > Version IDS 10 > > I am running a query something like this: > > table A (column s, column t, column u, column v) > table A has individual index on column s. > table A has another individual index on column t. > table A has another individual index on column u. > Update stats have run as high on these columns How many rows in table A? What are the distributions like on s, t, u? > table B (column w, column x) > table B has individual index on column w. > table B has another individual index on column x. > Update stats have run as high on these columns How many rows in table B? What are the distributions like on w? > select * from A, B where A.s = 1200 and B.w = A.t;> > Above query correctly takes INDEX PATH. ...on A.s, I assume. > Now I change the query to: > > select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u);> > set explain shows that it did SEQUENTIAL SCAN on table B.> > It's perplexing why would optimizer behave like this. It should be > taking index path for the second query too. What cost did SET EXPLAIN give you? Have you tried to force it to use an index scan with a hint? What cost did SET EXPLAIN give you now? Have you considered using: SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.T UNION -- ALL? SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.U It shouldn't be necessary - and the absence of 'ALL' might slow this down. Depending on the statistics and distributions, it might be reasonable to cost the sequential scan less than the indexed join - but I agree it is not all that likely. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/ publictimestamp.org/ptb/PTB-1963 tiger2 2007-12-06 21:00:03 29E0C81DABD186DDC84130B4E36174FFB2FCD866B598A5E7

On Dec 6, 2:33 pm, Jonathan Leffler <jleff...@earthlink.net> wrote: > mohitanch...@gmail.com wrote: > > Version IDS 10 > > > I am running a query something like this: > > > table A (column s, column t, column u, column v) > > table A has individual index on column s. > > table A has another individual index on column t. > > table A has another individual index on column u. > > Update stats have run as high on these columns > > How many rows in table A? What are the distributions like on s, t, u? > > > table B (column w, column x) > > table B has individual index on column w. > > table B has another individual index on column x. > > Update stats have run as high on these columns > > How many rows in table B? What are the distributions like on w? > > > select * from A, B where A.s = 1200 and B.w = A.t;> > > Above query correctly takes INDEX PATH. > > ...on A.s, I assume. > > > Now I change the query to: > > > select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u);> > > set explain shows that it did SEQUENTIAL SCAN on table B.> > > It's perplexing why would optimizer behave like this. It should be > > taking index path for the second query too. > > What cost did SET EXPLAIN give you? > Have you tried to force it to use an index scan with a hint? > What cost did SET EXPLAIN give you now? > > Have you considered using: > > SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.T > UNION -- ALL? > SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.U> > It shouldn't be necessary - and the absence of 'ALL' might slow this down. > > Depending on the statistics and distributions, it might be reasonable to > cost the sequential scan less than the indexed join - but I agree it is > not all that likely. > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleff...@earthlink.net, jleff...@us.ibm.com > Guardian of DBD::Informixv2007.0914 --http://dbi.perl.org/ > > publictimestamp.org/ptb/PTB-1963 tiger2 2007-12-06 21:00:03 > 29E0C81DABD186DDC84130B4E36174FFB2FCD866B598A5E7 Table A has around 20M rows and table B has around 130M rows, but table B is fragmented accross 15 dbspaces. Column in table B have High Mode, 0.500000 Resolution. It's really bizzare that optimizer is choosing squential scan when "OR" is added on on the same column. I ran update stats just before running the query too.

On Dec 6, 12:21 pm, mohitanch...@gmail.com wrote: > Version IDS 10 > > I am running a query something like this: > > table A (column s, column t, column u, column v) > table A has individual index on column s. > table A has another individual index on column t. > table A has another individual index on column u. > Update stats have run as high on these columns > > table B (column w, column x) > table B has individual index on column w. > table B has another individual index on column x. > Update stats have run as high on these columns > > select * from A, B where A.s = 1200 and B.w = A.t;> > Above query correctly takes INDEX PATH. > > Now I change the query to: > > select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u);> > set explain shows that it did SEQUENTIAL SCAN on table B.> > It's perplexing why would optimizer behave like this. It should be > taking index path for the second query too. How many different values does A.u have? if it has low number of distinct values then it might think that you will have to return most of the rows anyway.

On Dec 7, 11:42 am, bozon <cur...@crowson1.com> wrote: > On Dec 6, 12:21 pm, mohitanch...@gmail.com wrote: > > > > > > > Version IDS 10 > > > I am running a query something like this: > > > table A (column s, column t, column u, column v) > > table A has individual index on column s. > > table A has another individual index on column t. > > table A has another individual index on column u. > > Update stats have run as high on these columns > > > table B (column w, column x) > > table B has individual index on column w. > > table B has another individual index on column x. > > Update stats have run as high on these columns > > > select * from A, B where A.s = 1200 and B.w = A.t;> > > Above query correctly takes INDEX PATH. > > > Now I change the query to: > > > select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u);> > > set explain shows that it did SEQUENTIAL SCAN on table B.> > > It's perplexing why would optimizer behave like this. It should be > > taking index path for the second query too. > > How many different values does A.u have? if it has low number of > distinct values then it might think that you will have to return most > of the rows anyway.- Hide quoted text - > > - Show quoted text - It's a unique value

On Dec 7, 2:51 pm, mohitanch...@gmail.com wrote: > On Dec 7, 11:42 am, bozon <cur...@crowson1.com> wrote: > > > > > On Dec 6, 12:21 pm, mohitanch...@gmail.com wrote: > > > > Version IDS 10 > > > > I am running a query something like this: > > > > table A (column s, column t, column u, column v) > > > table A has individual index on column s. > > > table A has another individual index on column t. > > > table A has another individual index on column u. > > > Update stats have run as high on these columns > > > > table B (column w, column x) > > > table B has individual index on column w. > > > table B has another individual index on column x. > > > Update stats have run as high on these columns > > > > select * from A, B where A.s = 1200 and B.w = A.t;> > > > Above query correctly takes INDEX PATH. > > > > Now I change the query to: > > > > select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u);> > > > set explain shows that it did SEQUENTIAL SCAN on table B.> > > > It's perplexing why would optimizer behave like this. It should be > > > taking index path for the second query too. > > > How many different values does A.u have? if it has low number of > > distinct values then it might think that you will have to return most > > of the rows anyway.- Hide quoted text - > > > - Show quoted text - > > It's a unique value I think a better question is how unique is B.w? If there is only 1 match for the value of A.t and A.u then yes the seq scan seems od. However, it would appear that the optimizer is calculating that sequentially scaning the entire data portion of the table is faster then scanning the B.w index twice, as I believe when you use the "or" filter that gets turned into multiple index scans. So if B.w index was not very unique, then the optimizer might compute that doing the sequential scan costs less then scanning a very large index a 2nd time. You mentioned that table B is fragmented into 15 dbspaces, so is the index B.w a detached index that is not fragmented? If so, that would be a very large index to scan through multiple times if B.w wasn't very unique. Also, how is table B fragmented? Just round robin or some other strategy? When it says sequential scan does it say all or is it able to do some sort of fragment elimination? Although 1 thing to note is run time fragmentation elimination can be hard to see, and maybe not even possible, in just set explain output. The union suggestion would obviously get around this and again you could try using an optimizer directive like avoid full for table B to see if that might get you back to your index lookup for table B. I'd also be a bit curious of the performance difference.

On Dec 7, 1:43 pm, jpren...@yahoo.com wrote: > On Dec 7, 2:51 pm, mohitanch...@gmail.com wrote: > > > > > > > On Dec 7, 11:42 am, bozon <cur...@crowson1.com> wrote: > > > > On Dec 6, 12:21 pm, mohitanch...@gmail.com wrote: > > > > > Version IDS 10 > > > > > I am running a query something like this: > > > > > table A (column s, column t, column u, column v) > > > > table A has individual index on column s. > > > > table A has another individual index on column t. > > > > table A has another individual index on column u. > > > > Update stats have run as high on these columns > > > > > table B (column w, column x) > > > > table B has individual index on column w. > > > > table B has another individual index on column x. > > > > Update stats have run as high on these columns > > > > > select * from A, B where A.s = 1200 and B.w = A.t;> > > > > Above query correctly takes INDEX PATH. > > > > > Now I change the query to: > > > > > select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u);> > > > > set explain shows that it did SEQUENTIAL SCAN on table B.> > > > > It's perplexing why would optimizer behave like this. It should be > > > > taking index path for the second query too. > > > > How many different values does A.u have? if it has low number of > > > distinct values then it might think that you will have to return most > > > of the rows anyway.- Hide quoted text - > > > > - Show quoted text - > > > It's a unique value > > I think a better question is how unique is B.w? If there is only 1 > match for the value of A.t and A.u then yes the seq scan seems od. > However, it would appear that the optimizer is calculating that > sequentially scaning the entire data portion of the table is faster > then scanning the B.w index twice, as I believe when you use the "or" > filter that gets turned into multiple index scans. So if B.w index > was not very unique, then the optimizer might compute that doing the > sequential scan costs less then scanning a very large index a 2nd > time. > > You mentioned that table B is fragmented into 15 dbspaces, so is the > index B.w a detached index that is not fragmented? If so, that would > be a very large index to scan through multiple times if B.w wasn't > very unique. Also, how is table B fragmented? Just round robin or > some other strategy? When it says sequential scan does it say all or > is it able to do some sort of fragment elimination? Although 1 thing > to note is run time fragmentation elimination can be hard to see, and > maybe not even possible, in just set explain output. > > The union suggestion would obviously get around this and again you > could try using an optimizer directive like avoid full for table B to > see if that might get you back to your index lookup for table B. I'd > also be a bit curious of the performance difference.- Hide quoted text - > > - Show quoted text - It is odd because the value occurs only once in that table. That table is fragmented on column x based on for eg: mod(x, 15) = 0 ..x is a serial value. But, irrespetive of whether it's fragmented or not it should use index to locate the row. Why should an "or" condition activate sequential scan when head of the index is in where clause ? Union suggestion will not work for me because I was going to expand that sql into what I really want, but I am stuck at this bizarre situation.

On Dec 7, 4:39 pm, mohitanch...@gmail.com wrote: > On Dec 7, 1:43 pm, jpren...@yahoo.com wrote: > > > > > > > On Dec 7, 2:51 pm, mohitanch...@gmail.com wrote: > > > > On Dec 7, 11:42 am, bozon <cur...@crowson1.com> wrote: > > > > > On Dec 6, 12:21 pm, mohitanch...@gmail.com wrote: > > > > > > Version IDS 10 > > > > > > I am running a query something like this: > > > > > > table A (column s, column t, column u, column v) > > > > > table A has individual index on column s. > > > > > table A has another individual index on column t. > > > > > table A has another individual index on column u. > > > > > Update stats have run as high on these columns > > > > > > table B (column w, column x) > > > > > table B has individual index on column w. > > > > > table B has another individual index on column x. > > > > > Update stats have run as high on these columns > > > > > > select * from A, B where A.s = 1200 and B.w = A.t;> > > > > > Above query correctly takes INDEX PATH. > > > > > > Now I change the query to: > > > > > > select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u);> > > > > > set explain shows that it did SEQUENTIAL SCAN on table B.> > > > > > It's perplexing why would optimizer behave like this. It should be > > > > > taking index path for the second query too. > > > > > How many different values does A.u have? if it has low number of > > > > distinct values then it might think that you will have to return most > > > > of the rows anyway.- Hide quoted text - > > > > > - Show quoted text - > > > > It's a unique value > > > I think a better question is how unique is B.w? If there is only 1 > > match for the value of A.t and A.u then yes the seq scan seems od. > > However, it would appear that the optimizer is calculating that > > sequentially scaning the entire data portion of the table is faster > > then scanning the B.w index twice, as I believe when you use the "or" > > filter that gets turned into multiple index scans. So if B.w index > > was not very unique, then the optimizer might compute that doing the > > sequential scan costs less then scanning a very large index a 2nd > > time. > > > You mentioned that table B is fragmented into 15 dbspaces, so is the > > index B.w a detached index that is not fragmented? If so, that would > > be a very large index to scan through multiple times if B.w wasn't > > very unique. Also, how is table B fragmented? Just round robin or > > some other strategy? When it says sequential scan does it say all or > > is it able to do some sort of fragment elimination? Although 1 thing > > to note is run time fragmentation elimination can be hard to see, and > > maybe not even possible, in just set explain output. > > > The union suggestion would obviously get around this and again you > > could try using an optimizer directive like avoid full for table B to > > see if that might get you back to your index lookup for table B. I'd > > also be a bit curious of the performance difference.- Hide quoted text - > > > - Show quoted text - > > It is odd because the value occurs only once in that table. That table > is fragmented on column x based on for eg: mod(x, 15) = 0 ..x is a > serial value. But, irrespetive of whether it's fragmented or not it > should use index to locate the row. Why should an "or" condition > activate sequential scan when head of the index is in where clause ? > Union suggestion will not work for me because I was going to expand > that sql into what I really want, but I am stuck at this bizarre > situation.- Hide quoted text - > > - Show quoted text - I also forgot to mention that in sqexplain.out I get: SEQUENTIAL SCAN Filters: (B.w = A.t OR B.w = A.u) NESTED LOOP JOIN

On Dec 7, 7:39 pm, mohitanch...@gmail.com wrote: > On Dec 7, 1:43 pm, jpren...@yahoo.com wrote: > > > > > On Dec 7, 2:51 pm, mohitanch...@gmail.com wrote: > > > > On Dec 7, 11:42 am, bozon <cur...@crowson1.com> wrote: > > > > > On Dec 6, 12:21 pm, mohitanch...@gmail.com wrote: > > > > > > Version IDS 10 > > > > > > I am running a query something like this: > > > > > > table A (column s, column t, column u, column v) > > > > > table A has individual index on column s. > > > > > table A has another individual index on column t. > > > > > table A has another individual index on column u. > > > > > Update stats have run as high on these columns > > > > > > table B (column w, column x) > > > > > table B has individual index on column w. > > > > > table B has another individual index on column x. > > > > > Update stats have run as high on these columns > > > > > > select * from A, B where A.s = 1200 and B.w = A.t;> > > > > > Above query correctly takes INDEX PATH. > > > > > > Now I change the query to: > > > > > > select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u);> > > > > > set explain shows that it did SEQUENTIAL SCAN on table B.> > > > > > It's perplexing why would optimizer behave like this. It should be > > > > > taking index path for the second query too. > > > > > How many different values does A.u have? if it has low number of > > > > distinct values then it might think that you will have to return most > > > > of the rows anyway.- Hide quoted text - > > > > > - Show quoted text - > > > > It's a unique value > > > I think a better question is how unique is B.w? If there is only 1 > > match for the value of A.t and A.u then yes the seq scan seems od. > > However, it would appear that the optimizer is calculating that > > sequentially scaning the entire data portion of the table is faster > > then scanning the B.w index twice, as I believe when you use the "or" > > filter that gets turned into multiple index scans. So if B.w index > > was not very unique, then the optimizer might compute that doing the > > sequential scan costs less then scanning a very large index a 2nd > > time. > > > You mentioned that table B is fragmented into 15 dbspaces, so is the > > index B.w a detached index that is not fragmented? If so, that would > > be a very large index to scan through multiple times if B.w wasn't > > very unique. Also, how is table B fragmented? Just round robin or > > some other strategy? When it says sequential scan does it say all or > > is it able to do some sort of fragment elimination? Although 1 thing > > to note is run time fragmentation elimination can be hard to see, and > > maybe not even possible, in just set explain output. > > > The union suggestion would obviously get around this and again you > > could try using an optimizer directive like avoid full for table B to > > see if that might get you back to your index lookup for table B. I'd > > also be a bit curious of the performance difference.- Hide quoted text - > > > - Show quoted text - > > It is odd because the value occurs only once in that table. That table > is fragmented on column x based on for eg: mod(x, 15) = 0 ..x is a > serial value. But, irrespetive of whether it's fragmented or not it > should use index to locate the row. Why should an "or" condition > activate sequential scan when head of the index is in where clause ? > Union suggestion will not work for me because I was going to expand > that sql into what I really want, but I am stuck at this bizarre > situation So you are saying that column w in table B is a serial value and is unique or are you saying that column u in table A is the serial value? As for why would an or condition trigger a scan, it is possible. It's a cost based optimizer. It is calculating that the cost to sequential scan the table 1 time and apply the row level filter of B.w = (A.t or A.u) is smaller then the cost to scan the index twice, the first time applying the filter B.w = A.t and the second time where B.w = A.u. Again, if this is a large detached index, scaning through it when you are possibly returning a large number of rows can end up costing more then 1 sequential scan of the table. I'm not saying for sure it should be doing this in this case, but it is possible that that could be the better method. I'd like to see the entire set explain output and the entire server specific schema for both tables, and again like to have some idea how unique is any particular value in the B.w index. Or if you can't use the union, can you use optimizer directives? Example: select {+ AVOID_FULL(B)} * from A,B where A.s = 1200 and (B.w = A.t or B.w = A.u); In the set explain output you should see if it picks up the directive or not.

mohitanchlia@gmail.com schrieb: > On Dec 6, 2:33 pm, Jonathan Leffler <jleff...@earthlink.net> wrote: >> mohitanch...@gmail.com wrote: >>> Version IDS 10 >>> I am running a query something like this: >>> table A (column s, column t, column u, column v) >>> table A has individual index on column s. >>> table A has another individual index on column t. >>> table A has another individual index on column u. >>> Update stats have run as high on these columns >> How many rows in table A? What are the distributions like on s, t, u? >> >>> table B (column w, column x) >>> table B has individual index on column w. >>> table B has another individual index on column x. >>> Update stats have run as high on these columns >> How many rows in table B? What are the distributions like on w? >> >>> select * from A, B where A.s = 1200 and B.w = A.t;>>> Above query correctly takes INDEX PATH. >> ...on A.s, I assume. >> >>> Now I change the query to: >>> select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u); >>> set explain shows that it did SEQUENTIAL SCAN on table B.>>> It's perplexing why would optimizer behave like this. It should be >>> taking index path for the second query too. >> What cost did SET EXPLAIN give you? >> Have you tried to force it to use an index scan with a hint? >> What cost did SET EXPLAIN give you now? >> >> Have you considered using: >> >> SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.T >> UNION -- ALL? >> SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.U>> >> It shouldn't be necessary - and the absence of 'ALL' might slow this down. >> >> Depending on the statistics and distributions, it might be reasonable to >> cost the sequential scan less than the indexed join - but I agree it is >> not all that likely. >> >> -- >> Jonathan Leffler #include <disclaimer.h> >> Email: jleff...@earthlink.net, jleff...@us.ibm.com >> Guardian of DBD::Informixv2007.0914 --http://dbi.perl.org/ >> >> publictimestamp.org/ptb/PTB-1963 tiger2 2007-12-06 21:00:03 >> 29E0C81DABD186DDC84130B4E36174FFB2FCD866B598A5E7 > > Table A has around 20M rows and table B has around 130M rows, but > table B is fragmented accross 15 dbspaces. Column in table B have High > Mode, 0.500000 Resolution. It's really bizzare that optimizer is > choosing squential scan when "OR" is added on on the same column. I > ran update stats just before running the query too. Hi here are my 2 Eurocent: What the optimizer prefers is: scan B, indexread A - do a nested loop join I assume (but do not know) that you can read like 1000 pages belonging to table B from disk per second and fragment (given that you have READAHEAD around 4-6, not higher) I further assume, that RPP (rows per page) of table B is 20 (row size around 100 bytes) Your nrows of table B is 130.000.000 (BTW: does the optimizer know this, i.e. you did run update statistics low for table B, didn't you?) Now for your 15 frags you read 15.000 pages or 300.000 rows of table B per second So here we are: Pls test, if you can do a seqscan over table B in approx. 6,6 minutes. And then run update statistics low on table B, as I am quite sure now, that the poor optimizer does not know nrows of table B. But pls, read on! Now on the other hand we have: Indexread B, Indexread A - do a nested loop join Chances are (on modern HW, cheap (commodity HW) that is, no guaratnee that this is also true on big iron) that you can attach 100.000 BUFFERS per second I guess you indexes have 5 or 6 levels, including leaf level - let's go with 5 After a short while probability of buffer hit is I further assume that you can do a direct read from disk in 1000 mysec or 1 millisecond Level 0, 1, 2: 100% 3 x 1 mysec 3 mysec Level 3: 50% 501 mysec 501 mysec Level 4 (Leafes) 10% 901 mysec 901 mysec total per indexread: 1.405 Mysec The according avg access time you can see on right part of the line --> you can do 700 indexreads per second Therefore when you expect to hit 300.000 rows or more into result set, index based access is not as fast as a seqscan. Not for this situation, but for other queries this _is_ very important to keep in mind, as I always win bets with everyone and her sister, because few ppl 'd assume that the sweet spot is so low (300.000 is less than 0,3% of the table) HTH dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

On Dec 8, 12:00 pm, Richard Kofler <richard.kof...@chello.at> wrote: > mohitanch...@gmail.com schrieb: > > > > > On Dec 6, 2:33 pm, Jonathan Leffler <jleff...@earthlink.net> wrote: > >> mohitanch...@gmail.com wrote: > >>> Version IDS 10 > >>> I am running a query something like this: > >>> table A (column s, column t, column u, column v) > >>> table A has individual index on column s. > >>> table A has another individual index on column t. > >>> table A has another individual index on column u. > >>> Update stats have run as high on these columns > >> How many rows in table A? What are the distributions like on s, t, u? > > >>> table B (column w, column x) > >>> table B has individual index on column w. > >>> table B has another individual index on column x. > >>> Update stats have run as high on these columns > >> How many rows in table B? What are the distributions like on w? > > >>> select * from A, B where A.s = 1200 and B.w = A.t;> >>> Above query correctly takes INDEX PATH. > >> ...on A.s, I assume. > > >>> Now I change the query to: > >>> select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u); > >>> set explain shows that it did SEQUENTIAL SCAN on table B.> >>> It's perplexing why would optimizer behave like this. It should be > >>> taking index path for the second query too. > >> What cost did SET EXPLAIN give you? > >> Have you tried to force it to use an index scan with a hint? > >> What cost did SET EXPLAIN give you now? > > >> Have you considered using: > > >> SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.T > >> UNION -- ALL? > >> SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.U> > >> It shouldn't be necessary - and the absence of 'ALL' might slow this down. > > >> Depending on the statistics and distributions, it might be reasonable to > >> cost the sequential scan less than the indexed join - but I agree it is > >> not all that likely. > > >> -- > >> Jonathan Leffler #include <disclaimer.h> > >> Email: jleff...@earthlink.net, jleff...@us.ibm.com > >> Guardian of DBD::Informixv2007.0914 --http://dbi.perl.org/ > > >> publictimestamp.org/ptb/PTB-1963 tiger2 2007-12-06 21:00:03 > >> 29E0C81DABD186DDC84130B4E36174FFB2FCD866B598A5E7 > > > Table A has around 20M rows and table B has around 130M rows, but > > table B is fragmented accross 15 dbspaces. Column in table B have High > > Mode, 0.500000 Resolution. It's really bizzare that optimizer is > > choosing squential scan when "OR" is added on on the same column. I > > ran update stats just before running the query too. > > Hi > > here are my 2 Eurocent: > > What the optimizer prefers is: > > scan B, indexread A - do a nested loop join > > I assume (but do not know) that you can read > like 1000 pages belonging to table B from disk per second and fragment > (given that you have READAHEAD around 4-6, not higher) > > I further assume, that RPP (rows per page) of table B is 20 (row size around 100 bytes) > > Your nrows of table B is 130.000.000 (BTW: does the optimizer know this, i.e. > you did run update statistics low for table B, didn't you?) > > Now for your 15 frags you read 15.000 pages or 300.000 rows of table B per second > > So here we are: Pls test, if you can do a seqscan over table B in approx. 6,6 minutes. > And then run update statistics low on table B, as I am quite sure now, that > the poor optimizer does not know nrows of table B. > > But pls, read on! > > Now on the other hand we have: > > Indexread B, Indexread A - do a nested loop join > > Chances are (on modern HW, cheap (commodity HW) that is, no guaratnee that > this is also true on big iron) that you can attach 100.000 BUFFERS per second > > I guess you indexes have 5 or 6 levels, including leaf level - let's go with 5 > After a short while probability of buffer hit is > I further assume that you can do a direct read from disk in 1000 mysec or 1 millisecond > Level 0, 1, 2: 100% 3 x 1 mysec 3 mysec > Level 3: 50% 501 mysec 501 mysec > Level 4 (Leafes) 10% 901 mysec 901 mysec > total per indexread: 1.405 Mysec > > The according avg access time you can see on right part of the line > --> you can do 700 indexreads per second > > Therefore when you expect to hit 300.000 rows or more into result set, index based access > is not as fast as a seqscan. > Not for this situation, but for other queries this _is_ very important to keep in mind, > as I always win bets with everyone and her sister, because few ppl 'd assume that > the sweet spot is so low (300.000 is less than 0,3% of the table) > > HTH > dic_k > > -- > Richard Kofler > SOLID STATE EDV > Dienstleistungen GmbH > Vienna/Austria/Europe nice explanation. I have tried to explain this in the past before but have failed miserably. Nice to see a good explanation.

On Dec 7, 7:39 pm, mohitanch...@gmail.com wrote: > On Dec 7, 1:43 pm, jpren...@yahoo.com wrote: > > > > > On Dec 7, 2:51 pm, mohitanch...@gmail.com wrote: > > > > On Dec 7, 11:42 am, bozon <cur...@crowson1.com> wrote: > > > > > On Dec 6, 12:21 pm, mohitanch...@gmail.com wrote: > > > > > > Version IDS 10 > > > > > > I am running a query something like this: > > > > > > table A (column s, column t, column u, column v) > > > > > table A has individual index on column s. > > > > > table A has another individual index on column t. > > > > > table A has another individual index on column u. > > > > > Update stats have run as high on these columns > > > > > > table B (column w, column x) > > > > > table B has individual index on column w. > > > > > table B has another individual index on column x. > > > > > Update stats have run as high on these columns > > > > > > select * from A, B where A.s = 1200 and B.w = A.t;> > > > > > Above query correctly takes INDEX PATH. > > > > > > Now I change the query to: > > > > > > select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u);> > > > > > set explain shows that it did SEQUENTIAL SCAN on table B.> > > > > > It's perplexing why would optimizer behave like this. It should be > > > > > taking index path for the second query too. > > > > > How many different values does A.u have? if it has low number of > > > > distinct values then it might think that you will have to return most > > > > of the rows anyway.- Hide quoted text - > > > > > - Show quoted text - > > > > It's a unique value > > > I think a better question is how unique is B.w? If there is only 1 > > match for the value of A.t and A.u then yes the seq scan seems od. > > However, it would appear that the optimizer is calculating that > > sequentially scaning the entire data portion of the table is faster > > then scanning the B.w index twice, as I believe when you use the "or" > > filter that gets turned into multiple index scans. So if B.w index > > was not very unique, then the optimizer might compute that doing the > > sequential scan costs less then scanning a very large index a 2nd > > time. > > > You mentioned that table B is fragmented into 15 dbspaces, so is the > > index B.w a detached index that is not fragmented? If so, that would > > be a very large index to scan through multiple times if B.w wasn't > > very unique. Also, how is table B fragmented? Just round robin or > > some other strategy? When it says sequential scan does it say all or > > is it able to do some sort of fragment elimination? Although 1 thing > > to note is run time fragmentation elimination can be hard to see, and > > maybe not even possible, in just set explain output. > > > The union suggestion would obviously get around this and again you > > could try using an optimizer directive like avoid full for table B to > > see if that might get you back to your index lookup for table B. I'd > > also be a bit curious of the performance difference.- Hide quoted text - > > > - Show quoted text - > > It is odd because the value occurs only once in that table. That table > is fragmented on column x based on for eg: mod(x, 15) = 0 ..x is a > serial value. But, irrespetive of whether it's fragmented or not it > should use index to locate the row. Why should an "or" condition > activate sequential scan when head of the index is in where clause ? > Union suggestion will not work for me because I was going to expand > that sql into what I really want, but I am stuck at this bizarre > situation. Here are a couple ways to do it that may give different results. The second creates a view. Will this work to hid the union from the rest of the query? The first just rearranges things into an in clause. create table a( s int, t int, u int, v int ) ; create table b( w int, x int ) ; create index a_1x on a(s); create index a_2x on a(t); create index a_3x on a(u); create index b_1x on b(w); create index b_2x on b(x); update statistics high for table a ; update statistics high for table b ; select * from A, B where A.s = 1200 and b.w in (A.t, A.u) ; create view union_view(s, t, u, v, w, x) as select s, t, u, v, w, x from a, b where a.s = 1200 and b.w = a.t union -- all? select s, t, u, v, w, x from a, b where a.s = 1200 and b.w = a.u;

On 6 Dec, 17:21, mohitanch...@gmail.com wrote: > Version IDS 10 > > I am running a query something like this: > > table A (column s, column t, column u, column v) > table A has individual index on column s. > table A has another individual index on column t. > table A has another individual index on column u. > Update stats have run as high on these columns > > table B (column w, column x) > table B has individual index on column w. > table B has another individual index on column x. > Update stats have run as high on these columns > > select * from A, B where A.s = 1200 and B.w = A.t;> > Above query correctly takes INDEX PATH. > > Now I change the query to: > > select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u);> > set explain shows that it did SEQUENTIAL SCAN on table B.> > It's perplexing why would optimizer behave like this. It should be > taking index path for the second query too. an or results in the optimizor not using an index. change this to select * from A, B where A.s = 1200 and (B.w = A.t) union select * from A, B where A.s = 1200 and B.w = A.u); and it should use the index.

On Dec 6, 12:21 pm, mohitanch...@gmail.com wrote: > Version IDS 10 > > I am running a query something like this: > > table A (column s, column t, column u, column v) > table A has individual index on column s. > table A has another individual index on column t. > table A has another individual index on column u. > Update stats have run as high on these columns > > table B (column w, column x) > table B has individual index on column w. > table B has another individual index on column x. > Update stats have run as high on these columns > > select * from A, B where A.s = 1200 and B.w = A.t;> > Above query correctly takes INDEX PATH. > > Now I change the query to: > > select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u);> > set explain shows that it did SEQUENTIAL SCAN on table B.> > It's perplexing why would optimizer behave like this. It should be > taking index path for the second query too. Can you tell which indexes are unique and about how many different values per thousand (kind of like dbschema -d <database> -hd b and dbschema -d <database> -hd a) ? I would like to see what my optimizer suggests.

On Dec 8, 9:00 am, Richard Kofler <richard.kof...@chello.at> wrote: > mohitanch...@gmail.com schrieb: > > > > > > > On Dec 6, 2:33 pm, Jonathan Leffler <jleff...@earthlink.net> wrote: > >> mohitanch...@gmail.com wrote: > >>> Version IDS 10 > >>> I am running a query something like this: > >>> table A (column s, column t, column u, column v) > >>> table A has individual index on column s. > >>> table A has another individual index on column t. > >>> table A has another individual index on column u. > >>> Update stats have run as high on these columns > >> How many rows in table A? What are the distributions like on s, t, u? > > >>> table B (column w, column x) > >>> table B has individual index on column w. > >>> table B has another individual index on column x. > >>> Update stats have run as high on these columns > >> How many rows in table B? What are the distributions like on w? > > >>> select * from A, B where A.s = 1200 and B.w = A.t;> >>> Above query correctly takes INDEX PATH. > >> ...on A.s, I assume. > > >>> Now I change the query to: > >>> select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u); > >>> set explain shows that it did SEQUENTIAL SCAN on table B.> >>> It's perplexing why would optimizer behave like this. It should be > >>> taking index path for the second query too. > >> What cost did SET EXPLAIN give you? > >> Have you tried to force it to use an index scan with a hint? > >> What cost did SET EXPLAIN give you now? > > >> Have you considered using: > > >> SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.T > >> UNION -- ALL? > >> SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.U> > >> It shouldn't be necessary - and the absence of 'ALL' might slow this down. > > >> Depending on the statistics and distributions, it might be reasonable to > >> cost the sequential scan less than the indexed join - but I agree it is > >> not all that likely. > > >> -- > >> Jonathan Leffler #include <disclaimer.h> > >> Email: jleff...@earthlink.net, jleff...@us.ibm.com > >> Guardian of DBD::Informixv2007.0914 --http://dbi.perl.org/ > > >> publictimestamp.org/ptb/PTB-1963 tiger2 2007-12-06 21:00:03 > >> 29E0C81DABD186DDC84130B4E36174FFB2FCD866B598A5E7 > > > Table A has around 20M rows and table B has around 130M rows, but > > table B is fragmented accross 15 dbspaces. Column in table B have High > > Mode, 0.500000 Resolution. It's really bizzare that optimizer is > > choosing squential scan when "OR" is added on on the same column. I > > ran update stats just before running the query too. > > Hi > > here are my 2 Eurocent: > > What the optimizer prefers is: > > scan B, indexread A - do a nested loop join > > I assume (but do not know) that you can read > like 1000 pages belonging to table B from disk per second and fragment > (given that you have READAHEAD around 4-6, not higher) > > I further assume, that RPP (rows per page) of table B is 20 (row size around 100 bytes) > > Your nrows of table B is 130.000.000 (BTW: does the optimizer know this, i.e. > you did run update statistics low for table B, didn't you?) > > Now for your 15 frags you read 15.000 pages or 300.000 rows of table B per second > > So here we are: Pls test, if you can do a seqscan over table B in approx. 6,6 minutes. > And then run update statistics low on table B, as I am quite sure now, that > the poor optimizer does not know nrows of table B. > > But pls, read on! > > Now on the other hand we have: > > Indexread B, Indexread A - do a nested loop join > > Chances are (on modern HW, cheap (commodity HW) that is, no guaratnee that > this is also true on big iron) that you can attach 100.000 BUFFERS per second > > I guess you indexes have 5 or 6 levels, including leaf level - let's go with 5 > After a short while probability of buffer hit is > I further assume that you can do a direct read from disk in 1000 mysec or 1 millisecond > Level 0, 1, 2: 100% 3 x 1 mysec 3 mysec > Level 3: 50% 501 mysec 501 mysec > Level 4 (Leafes) 10% 901 mysec 901 mysec > total per indexread: 1.405 Mysec > > The according avg access time you can see on right part of the line > --> you can do 700 indexreads per second > > Therefore when you expect to hit 300.000 rows or more into result set, index based access > is not as fast as a seqscan. > Not for this situation, but for other queries this _is_ very important to keep in mind, > as I always win bets with everyone and her sister, because few ppl 'd assume that > the sweet spot is so low (300.000 is less than 0,3% of the table) > > HTH > dic_k > > -- > Richard Kofler > SOLID STATE EDV > Dienstleistungen GmbH > Vienna/Austria/Europe- Hide quoted text - > > - Show quoted text - After reading your message, I will confess that I didn't understand it completely but I did get a gist of it. So here is what I did: create table A_1( a serial, b integer, c integer); create table B_1( d serial, e integer); create index ix_tmp_a on A_1(a); create index ix_tmp_b on A_1(b); create index ix_tmp_c on A_1(c); create index ix_tmp_d on B_1(d); create index ix_tmp_e on B_1(e); insert into A_1 values (0,2,10); insert into A_1 values (0,3,11); insert into A_1 values (0,4,10); insert into A_1 values (0,5,11); insert into A_1 values (0,6,12); insert into A_1 values (0,7,13); insert into A_1 values (0,8,14); insert into A_1 values (0,9,15); insert into A_1 values (0,7,14); insert into A_1 values (0,6,16); insert into A_1 values (0,4,17); insert into A_1 values (0,3,18); insert into B_1 values (0,2); insert into B_1 values (0,3); insert into B_1 values (0,4); insert into B_1 values (0,5); insert into B_1 values (0,12); insert into B_1 values (0,13); insert into B_1 values (0,14); insert into B_1 values (0,15); insert into B_1 values (0,14); insert into B_1 values (0,16); insert into B_1 values (0,17); insert into B_1 values (0,18); insert into B_1 values (0,2); insert into B_1 values (0,11); insert into B_1 values (0,10); insert into B_1 values (0,11); insert into B_1 values (0,6); insert into B_1 values (0,7); insert into B_1 values (0,8); insert into B_1 values (0,9); insert into B_1 values (0,7); insert into B_1 values (0,6); insert into B_1 values (0,17); insert into B_1 values (0,18); update statistics low for table A_1; update statistics low for table B_1; update statistics high for table A_1; update statistics high for table B_1; select d from A_1, B_1 where a = 1 and e = b; select d from A_1, B_1 where a = 1 and (e = b or e = c); Here is the output from explain on. First query took index path, but as soon as I put or condition it took sequential scan. I would expect the optimizer to take index path. As I see it it's just an or condition but the search is still on same column which has index on it. RA_PAGES is set to 128 and RA_THRESHOLD is set

On Dec 11, 5:40 pm, mohitanch...@gmail.com wrote: > On Dec 8, 9:00 am, Richard Kofler <richard.kof...@chello.at> wrote: > > > > > mohitanch...@gmail.com schrieb: > > > > On Dec 6, 2:33 pm, Jonathan Leffler <jleff...@earthlink.net> wrote: > > >> mohitanch...@gmail.com wrote: > > >>> Version IDS 10 > > >>> I am running a query something like this: > > >>> table A (column s, column t, column u, column v) > > >>> table A has individual index on column s. > > >>> table A has another individual index on column t. > > >>> table A has another individual index on column u. > > >>> Update stats have run as high on these columns > > >> How many rows in table A? What are the distributions like on s, t, u? > > > >>> table B (column w, column x) > > >>> table B has individual index on column w. > > >>> table B has another individual index on column x. > > >>> Update stats have run as high on these columns > > >> How many rows in table B? What are the distributions like on w? > > > >>> select * from A, B where A.s = 1200 and B.w = A.t;> > >>> Above query correctly takes INDEX PATH. > > >> ...on A.s, I assume. > > > >>> Now I change the query to: > > >>> select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u); > > >>> set explain shows that it did SEQUENTIAL SCAN on table B.> > >>> It's perplexing why would optimizer behave like this. It should be > > >>> taking index path for the second query too. > > >> What cost did SET EXPLAIN give you? > > >> Have you tried to force it to use an index scan with a hint? > > >> What cost did SET EXPLAIN give you now? > > > >> Have you considered using: > > > >> SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.T > > >> UNION -- ALL? > > >> SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.U> > > >> It shouldn't be necessary - and the absence of 'ALL' might slow this down. > > > >> Depending on the statistics and distributions, it might be reasonable to > > >> cost the sequential scan less than the indexed join - but I agree it is > > >> not all that likely. > > > >> -- > > >> Jonathan Leffler #include <disclaimer.h> > > >> Email: jleff...@earthlink.net, jleff...@us.ibm.com > > >> Guardian of DBD::Informixv2007.0914 --http://dbi.perl.org/ > > > >> publictimestamp.org/ptb/PTB-1963 tiger2 2007-12-06 21:00:03 > > >> 29E0C81DABD186DDC84130B4E36174FFB2FCD866B598A5E7 > > > > Table A has around 20M rows and table B has around 130M rows, but > > > table B is fragmented accross 15 dbspaces. Column in table B have High > > > Mode, 0.500000 Resolution. It's really bizzare that optimizer is > > > choosing squential scan when "OR" is added on on the same column. I > > > ran update stats just before running the query too. > > > Hi > > > here are my 2 Eurocent: > > > What the optimizer prefers is: > > > scan B, indexread A - do a nested loop join > > > I assume (but do not know) that you can read > > like 1000 pages belonging to table B from disk per second and fragment > > (given that you have READAHEAD around 4-6, not higher) > > > I further assume, that RPP (rows per page) of table B is 20 (row size around 100 bytes) > > > Your nrows of table B is 130.000.000 (BTW: does the optimizer know this, i.e. > > you did run update statistics low for table B, didn't you?) > > > Now for your 15 frags you read 15.000 pages or 300.000 rows of table B per second > > > So here we are: Pls test, if you can do a seqscan over table B in approx. 6,6 minutes. > > And then run update statistics low on table B, as I am quite sure now, that > > the poor optimizer does not know nrows of table B. > > > But pls, read on! > > > Now on the other hand we have: > > > Indexread B, Indexread A - do a nested loop join > > > Chances are (on modern HW, cheap (commodity HW) that is, no guaratnee that > > this is also true on big iron) that you can attach 100.000 BUFFERS per second > > > I guess you indexes have 5 or 6 levels, including leaf level - let's go with 5 > > After a short while probability of buffer hit is > > I further assume that you can do a direct read from disk in 1000 mysec or 1 millisecond > > Level 0, 1, 2: 100% 3 x 1 mysec 3 mysec > > Level 3: 50% 501 mysec 501 mysec > > Level 4 (Leafes) 10% 901 mysec 901 mysec > > total per indexread: 1.405 Mysec > > > The according avg access time you can see on right part of the line > > --> you can do 700 indexreads per second > > > Therefore when you expect to hit 300.000 rows or more into result set, index based access > > is not as fast as a seqscan. > > Not for this situation, but for other queries this _is_ very important to keep in mind, > > as I always win bets with everyone and her sister, because few ppl 'd assume that > > the sweet spot is so low (300.000 is less than 0,3% of the table) > > > HTH > > dic_k > > > -- > > Richard Kofler > > SOLID STATE EDV > > Dienstleistungen GmbH > > Vienna/Austria/Europe- Hide quoted text - > > > - Show quoted text - > > After reading your message, I will confess that I didn't understand it > completely but I did get a gist of it. So here is what I did: > > create table A_1( a serial, > b integer, > c integer);> > create table B_1( d serial, > e integer);> > create index ix_tmp_a on A_1(a); > create index ix_tmp_b on A_1(b); > create index ix_tmp_c on A_1(c);> > create index ix_tmp_d on B_1(d); > create index ix_tmp_e on B_1(e);> > insert into A_1 values (0,2,10); > insert into A_1 values (0,3,11); > insert into A_1 values (0,4,10); > insert into A_1 values (0,5,11); > insert into A_1 values (0,6,12); > insert into A_1 values (0,7,13); > insert into A_1 values (0,8,14); > insert into A_1 values (0,9,15); > insert into A_1 values (0,7,14); > insert into A_1 values (0,6,16); > insert into A_1 values (0,4,17); > insert into A_1 values (0,3,18); > insert into B_1 values (0,2); > insert into B_1 values (0,3); > insert into B_1 values (0,4); > insert into B_1 values (0,5); > insert into B_1 values (0,12); > insert into B_1 values (0,13); > insert into B_1 values (0,14); > insert into B_1 values (0,15); > insert into B_1 values (0,14); > insert into B_1 values (0,16); > insert into B_1 values (0,17); > insert into B_1 values (0,18); > insert into B_1 values (0,2); > insert into B_1 values (0,11); > insert into B_1 values (0,10); > insert into B_1 values (0,11); > insert into B_1 values (0,6); > insert into B_1 values (0,7); > insert into B_1 values (0,8); > insert into B_1 values (0,9); > insert into B_1 values (0,7); > insert into B_1 values (0,6); > insert into B_1 values (0,17); > insert into B_1 values (0,18);> > update statistics low for table A_1; > update statistics low for table B_1;> > update statistics high for table A_1; > update statistics high for table B_1;> > select d from A_1, B_1 where a = 1 and e = b;> > select d from A_1, B_1 where a = 1 and (e = b or e = c);>@@N

On Dec 12, 11:36 am, jpren...@yahoo.com wrote: > On Dec 11, 5:40 pm, mohitanch...@gmail.com wrote: > > > > > On Dec 8, 9:00 am, Richard Kofler <richard.kof...@chello.at> wrote: > > > > mohitanch...@gmail.com schrieb: > > > > > On Dec 6, 2:33 pm, Jonathan Leffler <jleff...@earthlink.net> wrote: > > > >> mohitanch...@gmail.com wrote: > > > >>> Version IDS 10 > > > >>> I am running a query something like this: > > > >>> table A (column s, column t, column u, column v) > > > >>> table A has individual index on column s. > > > >>> table A has another individual index on column t. > > > >>> table A has another individual index on column u. > > > >>> Update stats have run as high on these columns > > > >> How many rows in table A? What are the distributions like on s, t, u? > > > > >>> table B (column w, column x) > > > >>> table B has individual index on column w. > > > >>> table B has another individual index on column x. > > > >>> Update stats have run as high on these columns > > > >> How many rows in table B? What are the distributions like on w? > > > > >>> select * from A, B where A.s = 1200 and B.w = A.t;> > > >>> Above query correctly takes INDEX PATH. > > > >> ...on A.s, I assume. > > > > >>> Now I change the query to: > > > >>> select * from A, B where A.s = 1200 and (B.w = A.t or B.w = A.u); > > > >>> set explain shows that it did SEQUENTIAL SCAN on table B.> > > >>> It's perplexing why would optimizer behave like this. It should be > > > >>> taking index path for the second query too. > > > >> What cost did SET EXPLAIN give you? > > > >> Have you tried to force it to use an index scan with a hint? > > > >> What cost did SET EXPLAIN give you now? > > > > >> Have you considered using: > > > > >> SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.T > > > >> UNION -- ALL? > > > >> SELECT * FROM A, B WHERE A.S = 1200 AND B.W = A.U> > > > >> It shouldn't be necessary - and the absence of 'ALL' might slow this down. > > > > >> Depending on the statistics and distributions, it might be reasonable to > > > >> cost the sequential scan less than the indexed join - but I agree it is > > > >> not all that likely. > > > > >> -- > > > >> Jonathan Leffler #include <disclaimer.h> > > > >> Email: jleff...@earthlink.net, jleff...@us.ibm.com > > > >> Guardian of DBD::Informixv2007.0914 --http://dbi.perl.org/ > > > > >> publictimestamp.org/ptb/PTB-1963 tiger2 2007-12-06 21:00:03 > > > >> 29E0C81DABD186DDC84130B4E36174FFB2FCD866B598A5E7 > > > > > Table A has around 20M rows and table B has around 130M rows, but > > > > table B is fragmented accross 15 dbspaces. Column in table B have High > > > > Mode, 0.500000 Resolution. It's really bizzare that optimizer is > > > > choosing squential scan when "OR" is added on on the same column. I > > > > ran update stats just before running the query too. > > > > Hi > > > > here are my 2 Eurocent: > > > > What the optimizer prefers is: > > > > scan B, indexread A - do a nested loop join > > > > I assume (but do not know) that you can read > > > like 1000 pages belonging to table B from disk per second and fragment > > > (given that you have READAHEAD around 4-6, not higher) > > > > I further assume, that RPP (rows per page) of table B is 20 (row size around 100 bytes) > > > > Your nrows of table B is 130.000.000 (BTW: does the optimizer know this, i.e. > > > you did run update statistics low for table B, didn't you?) > > > > Now for your 15 frags you read 15.000 pages or 300.000 rows of table B per second > > > > So here we are: Pls test, if you can do a seqscan over table B in approx. 6,6 minutes. > > > And then run update statistics low on table B, as I am quite sure now, that > > > the poor optimizer does not know nrows of table B. > > > > But pls, read on! > > > > Now on the other hand we have: > > > > Indexread B, Indexread A - do a nested loop join > > > > Chances are (on modern HW, cheap (commodity HW) that is, no guaratnee that > > > this is also true on big iron) that you can attach 100.000 BUFFERS per second > > > > I guess you indexes have 5 or 6 levels, including leaf level - let's go with 5 > > > After a short while probability of buffer hit is > > > I further assume that you can do a direct read from disk in 1000 mysec or 1 millisecond > > > Level 0, 1, 2: 100% 3 x 1 mysec 3 mysec > > > Level 3: 50% 501 mysec 501 mysec > > > Level 4 (Leafes) 10% 901 mysec 901 mysec > > > total per indexread: 1.405 Mysec > > > > The according avg access time you can see on right part of the line > > > --> you can do 700 indexreads per second > > > > Therefore when you expect to hit 300.000 rows or more into result set, index based access > > > is not as fast as a seqscan. > > > Not for this situation, but for other queries this _is_ very important to keep in mind, > > > as I always win bets with everyone and her sister, because few ppl 'd assume that > > > the sweet spot is so low (300.000 is less than 0,3% of the table) > > > > HTH > > > dic_k > > > > -- > > > Richard Kofler > > > SOLID STATE EDV > > > Dienstleistungen GmbH > > > Vienna/Austria/Europe- Hide quoted text - > > > > - Show quoted text - > > > After reading your message, I will confess that I didn't understand it > > completely but I did get a gist of it. So here is what I did: > > > create table A_1( a serial, > > b integer, > > c integer);> > > create table B_1( d serial, > > e integer);> > > create index ix_tmp_a on A_1(a); > > create index ix_tmp_b on A_1(b); > > create index ix_tmp_c on A_1(c);> > > create index ix_tmp_d on B_1(d); > > create index ix_tmp_e on B_1(e);> > > insert into A_1 values (0,2,10); > > insert into A_1 values (0,3,11); > > insert into A_1 values (0,4,10); > > insert into A_1 values (0,5,11); > > insert into A_1 values (0,6,12); > > insert into A_1 values (0,7,13); > > insert into A_1 values (0,8,14); > > insert into A_1 values (0,9,15); > > insert into A_1 values (0,7,14); > > insert into A_1 values (0,6,16); > > insert into A_1 values (0,4,17); > > insert into A_1 values (0,3,18); > > insert into B_1 values (0,2); > > insert into B_1 values (0,3); > > insert into B_1 values (0,4); > > insert into B_1 values (0,5); > > insert into B_1 values (0,12); > > insert into B_1 values (0,13); > > insert into B_1 values (0,14); > > insert into B_1 values (0,15); > > insert into B_1 values (0,14); > > insert into B_1 values (0,16); > > insert into B_1 values (0,17); > > insert into B_1 values (0,18); > > insert into B_1 values (0,2); > > insert into B_1 values (0,11); > > insert into B_1 values (0,10); > > insert into B_1 values (0,11); > > insert into B_1 values (0,6); > > insert into B_1 values (0,7); > > insert into B_1 values (0,8); > > insert into B_1 values (0,9); > > insert into B_1 values (0,7); > > insert into B_1 values (0,6); > > insert into B_1 values (0,17); > > insert into B_1 values (0,18);>@@NL@

my 2 0.01 Euro on it: i guess the or will cause that the optimizer can not determine how much of the data needs to be scanned. ... i guess the combination of a=1 and b=e will cause less to be scanned then 20 % so does a=1 and c=e however combine these 2 conditions.... that is harder to tell how much needs to be read. specially since one index is chosen to get the data. for b=e index an index on b would be the one, however for c=e an index on c would be the one. for both.... which index needs to be picked..... ???? dono if this is really in the optimizer but for a lot of databases: rule of the thumb if 20 % or more of the data needs to be scanned then a sequential scan is faster. therefor you are better off using a union as some others have suggested which uses the index!!! Superboer > SEQUENTIAL SCAN just because "OR" was added to it. Above is just an > example, same problem exist when those tables have 130 million rows.

I deleted a bunch of the back and forth above because my post is rather large and I want to save as many electrons as possible. > The problem with your example is that the optimizer is a cost based > optimizer. You can't really do a 20 row in each able test and compare > it to a 180 million row table and 30 million row table join. In your > 20 row test, a sequential scan is likely because all the rows in table > fit in 1 page so scanning 1 page for data and appying filters is > "cheaper" then scanning a 1 page index twice. So what might be > "cheapest" for a join with 2 small tables might not be what is > "cheapest" for 2 large tables, and vica versa. > > Have you used directives to force the optimizer to not sequentially > scan the 2nd table when you add the or clause? If so have you > compared the timings for both query runs? I did a much larger test and it gives me interesting results. I do think it is an issue. Summary: I inserted 250,000 rows into each table. I then did the query several different ways. Interestingly the sequential scan cost seems to be higher than the forced index cost 8990 versus 7504 (look for the directive). select d from A_1, B_1 where a = 1 and (e = b or e = c) Estimated Cost: 8990 Estimated # of Rows Returned: 999 Maximum Threads: 35 1) informix.a_1: INDEX PATH (1) Index Keys: a (Parallel, fragments: ALL) Lower Index Filter: informix.a_1.a = 1 2) informix.b_1: SEQUENTIAL SCAN versus QUERY: ------ select {+ avoid_full(B_1) } d from A_1, B_1 where a = 1 and e in (b, c) DIRECTIVES FOLLOWED: AVOID_FULL ( b_1 ) DIRECTIVES NOT FOLLOWED: Estimated Cost: 7504 Estimated # of Rows Returned: 999 Maximum Threads: 69 1) informix.a_1: INDEX PATH (1) Index Keys: a (Parallel, fragments: ALL) Lower Index Filter: informix.a_1.a = 1 2) informix.b_1: INDEX PATH Here is the complete test so it can be duplicated by others: Database selected. set explain on;Explain set. set pdqpriority 100;PDQ Priority set. -- SQL for rerun and to create the Sequence table the first time. { drop table a_1; drop table b_1; drop table sequence; create table sequence( seq integer ); create unique index sequence_0ux on sequence(seq); -- Whose dumb idea was it to make list use curly braces so you couldn't block un-comment it. insert into sequence(seq) select thousand*1000 + hundred*100 + ten*10 + unit + 1 from } --table(list{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}) as thousands(thousand), --table(list{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}) as hundreds(hundred), --table(list{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}) as tens(ten), --table(list{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}) as units(unit) --; { update statistics low for table sequence; update statistics high for table sequence;} create table A_1( a serial, b integer, c integer);Table created. create table B_1( d serial, e integer);Table created. insert into a_1 select 0, s1.seq, s2.seq from sequence s1, sequence s2 where s1.seq <= 500 and s2.seq <= 500 ;250000 row(s) inserted. insert into b_1 select 0, s1.seq from sequence s1, sequence s2 wheres1.seq <= 500 and s2.seq <= 500 ; 250000 row(s) inserted. create unique index ix_tmp_a on A_1(a);Index created. create index ix_tmp_b on A_1(b);Index created. create index ix_tmp_c on A_1(c);Index created. create unique index ix_tmp_d on B_1(d);Index created. create index ix_tmp_e on B_1(e);Index created. update statistics low for table A_1;Statistics updated. update statistics low for table B_1;Statistics updated. update statistics high for table A_1;Statistics updated. update statistics high for table B_1;Statistics updated. select current from table(list{1}); (expression) 2007-12-13 11:56:16.000 1 row(s) retrieved. unload to /dev/null select d from A_1, B_1 where a = 1 and e = b;500 row(s) unloaded. select current from table(list{1}); (expression) 2007-12-13 11:56:16.000 1 row(s) retrieved. unload to /dev/null select d from A_1, B_1 where a = 1 and (e = b ore = c); 500 row(s) unloaded. select current from table(list{1}); (expression) 2007-12-13 11:56:17.000 1 row(s) retrieved. unload to /dev/null select d from A_1, B_1 where a = 1 and e in (b,c) ; 500 row(s) unloaded. select current from table(list{1}); (expression) 2007-12-13 11:56:19.000 1 row(s) retrieved. unload to /dev/null select {+ avoid_full(B_1) } d from A_1, B_1 where a = 1 and e in (b, c) ;500 row(s) unloaded. select current from table(list{1}); (expression) 2007-12-13 11:56:26.000 1 row(s) retrieved. unload to /dev/null select d from A_1, B_1 where a = 1 and e = b union select d from A_1, B_1 where a = 1 and e = c ;500 row(s) unloaded. select current from table(list{1}); (expression) 2007-12-13 11:56:26.000 1 row(s) retrieved. Database closed. QUERY: ------ select current from table(list{1}) Estimated Cost: 1 Estimated # of Rows Returned: 1 Maximum Threads: 1 1) informix.unnamed_tbl_0: COLLECTION SCAN QUERY: ------ select d from A_1, B_1 where a = 1 and e = b Estimated Cost: 24 Estimated # of Rows Returned: 500 Maximum Threads: 69 1) informix.a_1: INDEX PATH (1) Index Keys: a (Parallel, fragments: ALL) Lower Index Filter: informix.a_1.a = 1 2) informix.b_1: INDEX PATH (1) Index Keys: e (Parallel, fragments: ALL) Lower Index Filter: informix.b_1.e = informix.a_1.b NESTED LOOP JOIN QUERY: ------ select current from table(list{1}) Estimated Cost: 1 Estimated # of Rows Returned: 1 Maximum Threads: 1 1) informix.unnamed_tbl_0: COLLECTION SCAN QUERY: ------ select d from A_1, B_1 where a = 1 and (e = b or e = c) Estimated Cost: 8990 Estimated # of Rows Returned: 999 Maximum Threads: 35 1) informix.a_1: INDEX PATH (1) Index Keys: a (Parallel, fragments: ALL) Lower Index Filter: informix.a_1.a = 1 2) informix.b_1: SEQUENTIAL SCAN Filters: (informix.b_1.e = informix.a_1.b OR informix.b_1.e = informix.a_1.c ) NESTED LOOP JOIN QUERY: ------ select current from table(list{1}) Estimated Cost: 1 Estimated # of Rows Returned: 1 Maximum Threads: 1 1) informix.unnamed_tbl_0: COLLECTION SCAN QUERY: ------ select d from A_1, B_1 where a = 1 and e in (b, c) Estimated Cost: 8990 Estimated # of Rows

On Dec 13, 7:39 am, Superboer <superbo...@t-online.de> wrote: > my 2 0.01 Euro on it: > > i guess the or will cause that the optimizer can not determine how > much of the data needs to be scanned. > ... i guess the combination of a=1 and b=e will cause less to be > scanned then 20 % > so does a=1 and c=e however combine these 2 conditions.... that is > harder to tell how much needs to be read. > specially since one index is chosen to get the data. > for b=e index an index on b would be the one, however for c=e an index > on c would be the one. > > for both.... which index needs to be picked..... ???? > > dono if this is really in the optimizer but for a lot of databases: > rule of the thumb if 20 % or more of the data needs to be scanned then > a sequential scan is faster. > > therefor you are better off using a union as some others have > suggested which uses the index!!! > > Superboer > > > SEQUENTIAL SCAN just because "OR" was added to it. Above is just an > > example, same problem exist when those tables have 130 million rows. I think forcing it with a directive is also a good option.

On Dec 13, 11:28 am, bozon <cur...@crowson1.com> wrote: > I deleted a bunch of the back and forth above because my post is > rather large and I want to save as many electrons as possible. > > > The problem with your example is that the optimizer is a cost based > > optimizer. You can't really do a 20 row in each able test and compare > > it to a 180 million row table and 30 million row table join. In your > > 20 row test, a sequential scan is likely because all the rows in table > > fit in 1 page so scanning 1 page for data and appying filters is > > "cheaper" then scanning a 1 page index twice. So what might be > > "cheapest" for a join with 2 small tables might not be what is > > "cheapest" for 2 large tables, and vica versa. > > > Have you used directives to force the optimizer to not sequentially > > scan the 2nd table when you add the or clause? If so have you > > compared the timings for both query runs? > > I did a much larger test and it gives me interesting results. I do > think it is an issue. > > Summary: > I inserted 250,000 rows into each table. > I then did the query several different ways. Interestingly the > sequential scan cost seems to be higher than the forced index cost > 8990 versus 7504 (look for the directive). > > select d from A_1, B_1 where a = 1 and (e = b or e = c)> > Estimated Cost: 8990 > Estimated # of Rows Returned: 999 > Maximum Threads: 35 > > 1) informix.a_1: INDEX PATH > > (1) Index Keys: a (Parallel, fragments: ALL) > Lower Index Filter: informix.a_1.a = 1 > > 2) informix.b_1: SEQUENTIAL SCAN > > versus > > QUERY: > ------ > select {+ avoid_full(B_1) } d from A_1, B_1 where a = 1 and e in (b, > c) > > DIRECTIVES FOLLOWED: > AVOID_FULL ( b_1 ) > DIRECTIVES NOT FOLLOWED:> > Estimated Cost: 7504 > Estimated # of Rows Returned: 999 > Maximum Threads: 69 > > 1) informix.a_1: INDEX PATH > > (1) Index Keys: a (Parallel, fragments: ALL) > Lower Index Filter: informix.a_1.a = 1 > > 2) informix.b_1: INDEX PATH > > Here is the complete test so it can be duplicated by others: > > Database selected. > > set explain on;> Explain set. > > set pdqpriority 100;> PDQ Priority set. > > -- SQL for rerun and to create the Sequence table the first time. > { > > drop table a_1; > drop table b_1;> > drop table sequence;> > create table sequence(> > seq integer > > ); > > create unique index sequence_0ux on sequence(seq);> > -- Whose dumb idea was it to make list use curly braces so you > couldn't block un-comment it. > insert into > sequence(seq) > select > > thousand*1000 + > > hundred*100 + > > ten*10 + > > unit + 1 > > from > > } > > --table(list{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}) as thousands(thousand), > > --table(list{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}) as hundreds(hundred), > > --table(list{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}) as tens(ten), > > --table(list{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}) as units(unit) > > --; > > { > update statistics low for table sequence; > update statistics high for table sequence;> > } > > create table A_1( a serial, > b integer, > c integer);> Table created. > > create table B_1( d serial, > e integer);> Table created. > > insert into a_1 select 0, s1.seq, s2.seq from sequence s1, sequence s2 > where s1.seq <= 500 and s2.seq <= 500 ;> 250000 row(s) inserted. > > insert into b_1 select 0, s1.seq from sequence s1, sequence s2 where> s1.seq <= 500 and s2.seq <= 500 ; > 250000 row(s) inserted. > > create unique index ix_tmp_a on A_1(a);> Index created. > > create index ix_tmp_b on A_1(b);> Index created. > > create index ix_tmp_c on A_1(c);> Index created. > > create unique index ix_tmp_d on B_1(d);> Index created. > > create index ix_tmp_e on B_1(e);> Index created. > > update statistics low for table A_1;> Statistics updated. > > update statistics low for table B_1;> Statistics updated. > > update statistics high for table A_1;> Statistics updated. > > update statistics high for table B_1;> Statistics updated. > > select current from table(list{1});> > (expression) > > 2007-12-13 11:56:16.000 > > 1 row(s) retrieved. > > unload to /dev/null select d from A_1, B_1 where a = 1 and e = b;> 500 row(s) unloaded. > > select current from table(list{1});> > (expression) > > 2007-12-13 11:56:16.000 > > 1 row(s) retrieved. > > unload to /dev/null select d from A_1, B_1 where a = 1 and (e = b or> e = c); > 500 row(s) unloaded. > > select current from table(list{1});> > (expression) > > 2007-12-13 11:56:17.000 > > 1 row(s) retrieved. > > unload to /dev/null select d from A_1, B_1 where a = 1 and e in (b,> c) ; > 500 row(s) unloaded. > > select current from table(list{1});> > (expression) > > 2007-12-13 11:56:19.000 > > 1 row(s) retrieved. > > unload to /dev/null select {+ avoid_full(B_1) } d from A_1, B_1 > where a = 1 and e in (b, c) ;> 500 row(s) unloaded. > > select current from table(list{1});> > (expression) > > 2007-12-13 11:56:26.000 > > 1 row(s) retrieved. > > unload to /dev/null > select d from A_1, B_1 where a = 1 and e = b > union > select d from A_1, B_1 where a = 1 and e = c ;> 500 row(s) unloaded. > > select current from table(list{1});> > (expression) > > 2007-12-13 11:56:26.000 > > 1 row(s) retrieved. > > Database closed. > > QUERY: > ------ > select current from table(list{1})> > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > Maximum Threads: 1 > > 1) informix.unnamed_tbl_0: COLLECTION SCAN > > QUERY: > ------ > select d from A_1, B_1 where a = 1 and e = b> > Estimated Cost: 24 > Estimated # of Rows Returned: 500 > Maximum Threads: 69 > > 1) informix.a_1: INDEX PATH > > (1) Index Keys: a (Parallel, fragments: ALL) > Lower Index Filter: informix.a_1.a = 1 > > 2) informix.b_1: INDEX PATH > > (1) Index Keys: e (Parallel, fragments: ALL) > Lower Index Filter: informix.b_1.e = informix.a_1.b > NESTED LOOP JOIN > > QUERY: > ------ > select current from table(list{1})> > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > Maximum Threads: 1 > > 1) informix.unnamed_tbl_0: COLLECTION SCAN > > QUERY: > ------ > select d from A_1, B_1 where a = 1 and (e = b or e = c)> > Estimated Cost: 8990 > Estimated # of Rows Returned: 999 > Maximum Threads: 35 > > 1) informix.a_1: INDEX PATH > > (1) Index Keys: a (Parallel, fragments: ALL) > Lower Index Filter: info

On Dec 13, 1:34 pm, jpren...@yahoo.com wrote: > On Dec 13, 11:28 am, bozon <cur...@crowson1.com> wrote: > > > I deleted a bunch of the back and forth above because my post is > > rather large and I want to save as many electrons as possible. > > > > The problem with your example is that the optimizer is a cost based > > > optimizer. You can't really do a 20 row in each able test and compare > > > it to a 180 million row table and 30 million row table join. In your > > > 20 row test, a sequential scan is likely because all the rows in table > > > fit in 1 page so scanning 1 page for data and appying filters is > > > "cheaper" then scanning a 1 page index twice. So what might be > > > "cheapest" for a join with 2 small tables might not be what is > > > "cheapest" for 2 large tables, and vica versa. > > > > Have you used directives to force the optimizer to not sequentially > > > scan the 2nd table when you add the or clause? If so have you > > > compared the timings for both query runs? > > > I did a much larger test and it gives me interesting results. I do > > think it is an issue. > > > Summary: > > I inserted 250,000 rows into each table. > > I then did the query several different ways. Interestingly the > > sequential scan cost seems to be higher than the forced index cost > > 8990 versus 7504 (look for the directive). > > > select d from A_1, B_1 where a = 1 and (e = b or e = c)> > > Estimated Cost: 8990 > > Estimated # of Rows Returned: 999 > > Maximum Threads: 35 > > > 1) informix.a_1: INDEX PATH > > > (1) Index Keys: a (Parallel, fragments: ALL) > > Lower Index Filter: informix.a_1.a = 1 > > > 2) informix.b_1: SEQUENTIAL SCAN > > > versus > > > QUERY: > > ------ > > select {+ avoid_full(B_1) } d from A_1, B_1 where a = 1 and e in (b, > > c) > > > DIRECTIVES FOLLOWED: > > AVOID_FULL ( b_1 ) > > DIRECTIVES NOT FOLLOWED:> > > Estimated Cost: 7504 > > Estimated # of Rows Returned: 999 > > Maximum Threads: 69 > > > 1) informix.a_1: INDEX PATH > > > (1) Index Keys: a (Parallel, fragments: ALL) > > Lower Index Filter: informix.a_1.a = 1 > > > 2) informix.b_1: INDEX PATH > > > Here is the complete test so it can be duplicated by others: > > > Database selected. > > > set explain on;> > Explain set. > > > set pdqpriority 100;> > PDQ Priority set. > > > -- SQL for rerun and to create the Sequence table the first time. > > { > > > drop table a_1; > > drop table b_1;> > > drop table sequence;> > > create table sequence(> > > seq integer > > > ); > > > create unique index sequence_0ux on sequence(seq);> > > -- Whose dumb idea was it to make list use curly braces so you > > couldn't block un-comment it. > > insert into > > sequence(seq) > > select > > > thousand*1000 + > > > hundred*100 + > > > ten*10 + > > > unit + 1 > > > from > > > } > > > --table(list{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}) as thousands(thousand), > > > --table(list{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}) as hundreds(hundred), > > > --table(list{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}) as tens(ten), > > > --table(list{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}) as units(unit) > > > --; > > > { > > update statistics low for table sequence; > > update statistics high for table sequence;> > > } > > > create table A_1( a serial, > > b integer, > > c integer);> > Table created. > > > create table B_1( d serial, > > e integer);> > Table created. > > > insert into a_1 select 0, s1.seq, s2.seq from sequence s1, sequence s2 > > where s1.seq <= 500 and s2.seq <= 500 ;> > 250000 row(s) inserted. > > > insert into b_1 select 0, s1.seq from sequence s1, sequence s2 where> > s1.seq <= 500 and s2.seq <= 500 ; > > 250000 row(s) inserted. > > > create unique index ix_tmp_a on A_1(a);> > Index created. > > > create index ix_tmp_b on A_1(b);> > Index created. > > > create index ix_tmp_c on A_1(c);> > Index created. > > > create unique index ix_tmp_d on B_1(d);> > Index created. > > > create index ix_tmp_e on B_1(e);> > Index created. > > > update statistics low for table A_1;> > Statistics updated. > > > update statistics low for table B_1;> > Statistics updated. > > > update statistics high for table A_1;> > Statistics updated. > > > update statistics high for table B_1;> > Statistics updated. > > > select current from table(list{1});> > > (expression) > > > 2007-12-13 11:56:16.000 > > > 1 row(s) retrieved. > > > unload to /dev/null select d from A_1, B_1 where a = 1 and e = b;> > 500 row(s) unloaded. > > > select current from table(list{1});> > > (expression) > > > 2007-12-13 11:56:16.000 > > > 1 row(s) retrieved. > > > unload to /dev/null select d from A_1, B_1 where a = 1 and (e = b or> > e = c); > > 500 row(s) unloaded. > > > select current from table(list{1});> > > (expression) > > > 2007-12-13 11:56:17.000 > > > 1 row(s) retrieved. > > > unload to /dev/null select d from A_1, B_1 where a = 1 and e in (b,> > c) ; > > 500 row(s) unloaded. > > > select current from table(list{1});> > > (expression) > > > 2007-12-13 11:56:19.000 > > > 1 row(s) retrieved. > > > unload to /dev/null select {+ avoid_full(B_1) } d from A_1, B_1 > > where a = 1 and e in (b, c) ;> > 500 row(s) unloaded. > > > select current from table(list{1});> > > (expression) > > > 2007-12-13 11:56:26.000 > > > 1 row(s) retrieved. > > > unload to /dev/null > > select d from A_1, B_1 where a = 1 and e = b > > union > > select d from A_1, B_1 where a = 1 and e = c ;> > 500 row(s) unloaded. > > > select current from table(list{1});> > > (expression) > > > 2007-12-13 11:56:26.000 > > > 1 row(s) retrieved. > > > Database closed. > > > QUERY: > > ------ > > select current from table(list{1})> > > Estimated Cost: 1 > > Estimated # of Rows Returned: 1 > > Maximum Threads: 1 > > > 1) informix.unnamed_tbl_0: COLLECTION SCAN > > > QUERY: > > ------ > > select d from A_1, B_1 where a = 1 and e = b> > > Estimated Cost: 24 > > Estimated # of Rows Returned: 500 > > Maximum Threads: 69 > > > 1) informix.a_1: INDEX PATH > > > (1) Index Keys: a (Parallel, fragments: ALL) > > Lower Index Filter: informix.a_1.a = 1 > > > 2) informix.b_1: INDEX PATH > > > (1) Index Keys: e (Parallel, fragments: ALL) > > Lower Index Filter: informix.b_1.e = informix.a_1.b > > NESTED LOOP JOIN > > > QUERY: > > ------ > > select current from table(list{1})> > > Estimated Cost: 1 > > Estimated # of Rows Returned: 1 > > Maximum Threads

Please do check it out. I don't remember it being 7 seconds to do that section but it does look like it. I am on vacation and I don't have an Informix instance at home to try this on. I worried about the pdqpriority too. I should have left it off but I am in the habit of turning it on when I build indexes which I did when I built the 2 tables and the sequence table.

On Dec 13, 4:49 pm, bozon <cur...@crowson1.com> wrote: > Please do check it out. I don't remember it being 7 seconds to do that > section but it does look like it. I am on vacation and I don't have > an Informix instance at home to try this on. I worried about the > pdqpriority too. I should have left it off but I am in the habit of > turning it on when I build indexes which I did when I built the 2 > tables and the sequence table. Well I haven't tried it yet, but I did finally realize what I thought was odd about the set explain output for the optimizer directive case (other then the max threads issue) and that was even though it says it's using the index, it doesn't appear to be applying the join filter at the index level, it's applying the filter at the data level. I explected the set explain output to look like this: select {+ avoid_full(B_1) } d from A_1, B_1 where a = 1 and e in (b, c) DIRECTIVES FOLLOWED: AVOID_FULL ( b_1 ) DIRECTIVES NOT FOLLOWED: Estimated Cost: 7504 Estimated # of Rows Returned: 999 Maximum Threads: 69 1) informix.a_1: INDEX PATH (1) Index Keys: a (Parallel, fragments: ALL) Lower Index Filter: informix.a_1.a = 1 2) informix.b_1: INDEX PATH (1) Index Keys: e (Parallel, fragments: ALL) Lower Index Filter: informix.b_1.e = informix.a_1.b (2) Index Key: e (Parallel, fragments: ALL) Lower Index Filter: informix.b_1.e = informix.a_1.c NESTED LOOP JOIN That would be using the index but having to scan through it twice to satisfy the join filter. So I guess I'm not sure why the optimizer seems to not be considering using the index on the join when an OR clause is used. It doesn't appear as though the optimizer directive helps that out, and only using the union seems to be a workaround.

On Dec 14, 4:19 pm, jpren...@yahoo.com wrote: > On Dec 13, 4:49 pm, bozon <cur...@crowson1.com> wrote: > > > Please do check it out. I don't remember it being 7 seconds to do that > > section but it does look like it. I am on vacation and I don't have > > an Informix instance at home to try this on. I worried about the > > pdqpriority too. I should have left it off but I am in the habit of > > turning it on when I build indexes which I did when I built the 2 > > tables and the sequence table. > > Well I haven't tried it yet, but I did finally realize what I thought > was odd about the set explain output for the optimizer directive case > (other then the max threads issue) and that was even though it says > it's using the index, it doesn't appear to be applying the join filter > at the index level, it's applying the filter at the data level. > > I explected the set explain output to look like this: > > select {+ avoid_full(B_1) } d from A_1, B_1 where a = 1 and e in (b, > c) > > DIRECTIVES FOLLOWED: > AVOID_FULL ( b_1 ) > DIRECTIVES NOT FOLLOWED:> > Estimated Cost: 7504 > Estimated # of Rows Returned: 999 > Maximum Threads: 69 > > 1) informix.a_1: INDEX PATH > > (1) Index Keys: a (Parallel, fragments: ALL) > Lower Index Filter: informix.a_1.a = 1 > > 2) informix.b_1: INDEX PATH > > (1) Index Keys: e (Parallel, fragments: ALL) > Lower Index Filter: informix.b_1.e = informix.a_1.b > > (2) Index Key: e (Parallel, fragments: ALL) > Lower Index Filter: informix.b_1.e = informix.a_1.c > NESTED LOOP JOIN > > That would be using the index but having to scan through it twice to > satisfy the join filter. > > So I guess I'm not sure why the optimizer seems to not be considering > using the index on the join when an OR clause is used. It doesn't > appear as though the optimizer directive helps that out, and only > using the union seems to be a workaround. I wonder if I had said use index instead of avoid _full would it have done anything differently. I am on vacation and I don't want to crank anything up at this point to see what is going on. mohitanch can do it.