WG: Query Optimization: Wrong execution plan?

Dynamic hash join is a relatively new idee. You can refuse on it, if you set the variable OPTCOMPIND to 0 in you onconfig - file. If it doesn't work, try to use optimizer direktives (only 7.30 and higher). Best regards - Denis > ---------- > Von: Xavi Tarafa Mercader[SMTP:xavier.tarafa@corp.terra.com] > Antwort an: Xavi Tarafa Mercader > Gesendet: Mittwoch, 16. August 2000 09:14 > An: informix-list@iiug.org > Betreff: Query Optimization: Wrong execution plan? > > Hello All, > > I have a problem with a query: > > The query is the join of two tables > > select * from tab1, tab2 > where tab1.pk=tab2.fk> > There're indexes on both fields (unique index on tab1.pk and indexes > with duplicates on tab2.fk). > > The problem is that the query takes too much time. > > The the tables sizes are: tab2: 5'5 Milions rows, > tab1: 175.000 rows. > > I've executed UPDATE STATISTICS medium for all the datbase and high for > those fields involved on the query. > > I've looked at the plan chossen and it is as follows: > > Estimated Cost: 714082 > Estimated # of Rows Returned: 5534351 > > 1) informix.tab1: SEQUENTIAL SCAN > > 2) informix.tab2: SEQUENTIAL SCAN > > > DYNAMIC HASH JOIN > Dynamic Hash Filters: informix.tab2.fk= informix.tab1.pk > > I've the following questions, > > Why does the plan read sequentially both table instead of reading one > using the index? I think it would be faster. > > How does a "DYNAMIC HASH JOIN" work? > > Any suggestion about how to improve the query performance? > > Thank you, > > Xavi. >

I'm not sure what the performance will be with the index but I would guess: 1) scan tab1 and probe on t2 -> seq scan on tab1 + 5.5m random I/Os to tab2 2) scan tab2 and probe into t1-> sec scan on tab2 + 175000 random I/Os on tab1 I would expect here 2) to do better. However in a hash join (and this is dependent on this thing to actually be able to hold tab1 in memory, or mostly in memory) I would do: seq scan on tab1 and throw the tuples into N buckets based on the key value. The optimzer would first read this smaller table to build the buckets. Then do a seq scan on tab2, hash the key , and find in bucket the tuple in tab1 if present, output the value, otherwise discard. Note that the assumption is that N and 175000 comes out to be as large as possible a number of buckets, this reduces searches in each bucket. This could make the computation part run in pretty much O(1) time (a hash+ search in (small) bucket) and can make the whole thing run in time proportional to a scan in tab1+tab2 assuming the engine has memory to keep the buckets. I would expect this hash join to run faster as compared to index probes and I would expect the optimizer to have done a good job since you have no other restricting condition in the query that changes the selectivity to a smaller percentage than 100% for one of the tables in the query. Caveat: Not sure how informix implemented this actually, so I could be wrong. If you get it to work with the index, could you post a followup comparing the execution times between the hash join and the indexed version? Thanks Michael On Wed, 16 Aug 2000, Volkov Denis wrote: > > Dynamic hash join is a relatively new idee. > You can refuse on it, if you set the variable OPTCOMPIND to 0 in you > onconfig - file. If it doesn't work, try to use optimizer direktives (only > 7.30 and higher). > > Best regards > > - Denis > > > ---------- > > Von: Xavi Tarafa Mercader[SMTP:xavier.tarafa@corp.terra.com] > > Antwort an: Xavi Tarafa Mercader > > Gesendet: Mittwoch, 16. August 2000 09:14 > > An: informix-list@iiug.org > > Betreff: Query Optimization: Wrong execution plan? > > > > Hello All, > > > > I have a problem with a query: > > > > The query is the join of two tables > > > > select * from tab1, tab2 > > where tab1.pk=tab2.fk> > > > There're indexes on both fields (unique index on tab1.pk and indexes > > with duplicates on tab2.fk). > > > > The problem is that the query takes too much time. > > > > The the tables sizes are: tab2: 5'5 Milions rows, > > tab1: 175.000 rows. > > > > I've executed UPDATE STATISTICS medium for all the datbase and high for > > those fields involved on the query. > > > > I've looked at the plan chossen and it is as follows: > > > > Estimated Cost: 714082 > > Estimated # of Rows Returned: 5534351 > > > > 1) informix.tab1: SEQUENTIAL SCAN > > > > 2) informix.tab2: SEQUENTIAL SCAN > > > > > > DYNAMIC HASH JOIN > > Dynamic Hash Filters: informix.tab2.fk= informix.tab1.pk > > > > I've the following questions, > > > > Why does the plan read sequentially both table instead of reading one > > using the index? I think it would be faster. > > > > How does a "DYNAMIC HASH JOIN" work? > > > > Any suggestion about how to improve the query performance? > > > > Thank you, > > > > Xavi. > > > --- Michael Ortega-Binderberger miki@acm.org, miki@ics.uci.edu miki@computer.org, m.ortega@ieee.org Department of Computer Science http://www-db.ics.uci.edu/~miki U. of Illinois, Urbana Champaign 949-824-7231 fax: 949-824-4056 444 Computer Science, On loan to the University of U of California at Irvine, California, Irvine (Researcher) Irvine, CA, 92697-3425