db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6317) Optmizer can choose the wrong path when BTreeCostController.java returns an estimate cost and row count of 0.0
Date Thu, 29 Aug 2013 04:49:51 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6317?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13753303#comment-13753303
] 

Mamta A. Satoor commented on DERBY-6317:
----------------------------------------

I am trying to see if I can reproduce the problem. I am trying it right now with 8million
rows in the table which I think looks like the table Brett has but with 48million rows. When
I tried 800,000 rows, the problem did not reproduce. I will post the result of that run when
it is finished but I wanted to share the test code to make sure I am mimicking Brett's tables
and indexes correctly. Please let me know if the test code needs to be changed to match the
problem schema. I have also added a println in BTreeCostController.getScanCost as follows
            if (ret_fraction < 0) {
                //added following println
            	System.out.println("In engine code with ret_fraction<0");
                ret_fraction = 0;
            }
I think when the test runs into estimated row count of 0, we will hit the piece of code above
and will see the println printed.

Run the program as follows
java  org.apache.derbyTesting.functionTests.tests.lang.MamtaJDBC  > dellater.txt

The output from the program will look as follows
Testing embedded
finished creating tables
Table1 -    done inserting data
Table3 - done inserting data
Table2 - done inserting data
done inserting data
t0.Table3_ID =0
t0.Table3_ID =1
t0.Table3_ID =2
.....

And hopefully at some point, we will see "In engine code with ret_fraction<0" in the output
printed by the test program.



package org.apache.derbyTesting.functionTests.tests.lang;

import java.net.*; 
import java.io.*; 
import java.sql.*;

/** 
 * Client template starts its own NetworkServer and runs some SQL against it. 
 * The SQL or JDBC API calls can be modified to reproduce issues 
 * 
 */
public class MamtaJDBC { 
	
    public static void main(String[] args) throws Exception {
    	//Make Derby use index
    	Connection conn=null;
    	Statement s;
    	PreparedStatement ps;
        	System.out.println("Testing embedded");
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
            try{
                conn = DriverManager.getConnection("jdbc:derby:c:/dellater/ClobTest3;create=true");
            }catch(Exception ex){}
    	s = conn.createStatement();
        try { 
            s.executeUpdate("DROP TABLE Table2"); 
        } catch (SQLException se) { 
            if (!se.getSQLState().equals("42Y55")) 
                throw se; 
        } 
        try { 
            s.executeUpdate("DROP TABLE Table1"); 
        } catch (SQLException se) { 
            if (!se.getSQLState().equals("42Y55")) 
                throw se; 
        } 
        try { 
            s.executeUpdate("DROP TABLE Table3"); 
        } catch (SQLException se) { 
            if (!se.getSQLState().equals("42Y55")) 
                throw se; 
        }
        //equivalent to Bret's table "CORE_V1"."CONFIGURATION_BUNDLE"
        s.executeUpdate("CREATE TABLE Table1 ("+
        		"ID int PRIMARY KEY NOT NULL)"); 
        //equivalent to Bret's table "PKG_9145E_V1"."COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY"
        s.executeUpdate("CREATE TABLE Table2 ("+
        		"Table1_ID int NOT NULL,"+
        		"Table3_ID int NOT NULL,"+
        		"CONSTRAINT TABLE2_PK PRIMARY KEY "+
        		"(Table1_ID,Table3_ID))");
        //equivalent to Bret's table "PKG_9145E_V1"."COS_ED_DROP_PROFILE_DSCP_QMAPPING"
        s.executeUpdate("CREATE TABLE Table3 ("+
        		"ID int PRIMARY KEY NOT NULL)"); 
        
        s.executeUpdate("ALTER TABLE table2 "+
        		"ADD CONSTRAINT TABLE2_FK_1 "+
        		"FOREIGN KEY (Table1_ID) "+
        		"REFERENCES TABLE1(ID)");
        s.executeUpdate("ALTER TABLE table2 "+
        		"ADD CONSTRAINT TABLE2_FK_2 "+
        		"FOREIGN KEY (Table3_ID) "+
        		"REFERENCES TABLE3(ID)");
        System.out.println("finished creating tables");
	      int count = 1000000; 
	      ps = conn.prepareStatement("INSERT INTO table1 VALUES (?)");
  	      for (int i = 0; i < count; i++) { 
  	    	  ps.setInt(1,i);
  	        ps.execute();
	      } 
  	      System.out.println("Table1 -    done inserting data");
	      count = 8000000; 
	      ps = conn.prepareStatement("INSERT INTO table3 VALUES (?)");
  	      for (int i = 0; i < count; i++) { 
  	    	  ps.setInt(1,i);
    	        ps.execute();
	      } 
  	      System.out.println("Table3 - done inserting data");
	      count = 1000000; 
	      ps = conn.prepareStatement("INSERT INTO table2 VALUES (?,?)");
  	      for (int i = 0; i < count; i++) { 
	  	    	  ps.setInt(1,i);
  	    	  int j=0;
  	    	  for (int k=0; k<8; k++,j++) {
  	    		  ps.setInt(2,j);
  	              ps.execute();
  	    	  }
	      } 
  	      System.out.println("Table2 - done inserting data");
  	      System.out.println("done inserting data");
	      count = 8000000; 
  	      for (int i = 0; i < count; i++) { 
  	    	  System.out.println("t0.Table3_ID ="+i);
  	    	  s.execute("SELECT * FROM "+
  	    	    	  "Table1 T1,"+
     			  "Table2 t0 "+
  	    	    	  "WHERE t1.ID = t0.Table1_ID and "+
     			  "t0.Table3_ID = "+i);
  	      }
  	      System.out.println("done testing");
    }
}
                
> Optmizer can choose the wrong path when BTreeCostController.java returns an estimate
cost and row count of 0.0
> --------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-6317
>                 URL: https://issues.apache.org/jira/browse/DERBY-6317
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.8.2.2
>         Environment: Derby 10.8.2.2 on Oracle Solaris 10 
>            Reporter: Brett Bergquist
>            Assignee: Mike Matrigali
>         Attachments: derby6317_2.diff, derby6317.diff
>
>
> The optimizer can chose the wrong path when BTreeCostController.java returns an estimate
cost and row count of 0.0.  
> Assume that you have two tables that are being joined like:
> SELECT * FROM T1, T0
> WHERE T1.ID = T0.F_ID and
> T0.ID = 3;
> Also assume that T0 has two columns, ID and F_ID and F_ID is a foreign key on T1.ID.
  Assume that T1.ID is the primary key of T1 and (T0.F_ID, T0.ID) is the primary key on T0.
 Assume that there is a non-unique index on T0.ID.
> The correct query plan for this should be to query T0 using the non-unique index on T0.ID
and then use the foreign key value in those rows to do query T1 using the primary key on T1.
> With some values of T0.ID in the above query this query plan is chosen and works.  With
other values of T0.ID , the query plan does an query on T0 using the non-unique index on T0.ID
and then does a table scan on T1.
> For example, in my case the query:
> SELECT * FROM T1, T0
> WHERE T1.ID = T0.F_ID and
> T0.ID = 22112129;
> has this query plan.   
> The problem appears to be in BTreeCostController.java.  When this returns the same value
for the "left_of_start" and the "left_of_stop" (which is being used to estimate the number
of rows and cost), then the estimate cost and row count becomes 0.0.   When this is used in
the join order of T0, T1, then the cost of the table scan for T1 becomes 0.0 as well.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message