Re: [sqlite] Automatic join reordering doesn't seem to work?

[Ryan Johnson]

On 23/01/2012 12:51 PM, Richard Hipp wrote:
On Mon, Jan 23, 2012 at 12:48 PM, Simon Slavin<slav...@bigfraud.org>  wrote:
On 23 Jan 2012, at 4:30pm, Ryan Johnson wrote:

Bump?


   I don't know if Dr Hipp is pursuing this privately or expecting it to be
solved collaboratively on this list.


I don't have a test case to work on.
My database file is ~150MB, but was generated by the TPC-H data 
generator program. Assuming a linux-like environment (including cygwin), 
the following will reproduce the setup in under five minutes:

1. Download http://www.tpc.org/tpch/spec/tpch_2_13_0.tar.gz and extract
   the files (warning: they unpack to the current directory by default)
2. Copy/rename makefile.suite to Makefile
3. Edit 'Makefile' and set the following four variables (about 100
   lines into the file, just past the revision history):
     * CC=$YOUR_COMPILER
     * DATABASE=SQLSERVER
     * MACHINE=LINUX
     * WORKLOAD=TPCH
4. Edit 'driver.c' and divide all large magic numbers by 10 (lines
   181-195):
     * Example: {"part.tbl", "part table", 20000, /* originally 200000 */
5. Invoke 'make'
6. Invoke './dbgen -vf' (creates several .tbl files)
7. Create a new sqlite3 database using the attached tpch-schema.ddl file
8. Use .import to load customer.tbl and orders.tbl into the tables with
   the same name
9. Run ANALYZE (doesn't seem to make a difference)
10. Run the problem queries from the OP

The problem persists with a freshly-generated database on my machine, 
using a just-compiled sqlite-3.7.10.

Regards,
Ryan

create table Part(
        partkey INTEGER PRIMARY KEY,
        name,
        mfgr,
        brand,
        type,
        size INTEGER,
        container,
        retailprice REAL,
        comment,
        dummy);

create table Supplier(
        suppKey INTEGER PRIMARY KEY,
        name, address,
        nationkey INTEGER,
        phone,
        acctbal REAL,
        comment,
        dummy);

create table PartSupp(
        partKey INTEGER,
        suppKey INTEGER,
        availqty  INTEGER,
        supplycost REAL,
        comment,
        dummy);

create table Customer(
       CustKey INTEGER PRIMARY KEY,
       name,
       address,
       nationkey INTEGER,
       phone,
       acctbal REAL,
       mktsegment,
       comment,
        dummy);

create table Nation(
        nationkey  INTEGER PRIMARY KEY,
        name,
        regionkey INTEGER,
        comment,
        dummy);

create table Region(
        regionkey INTEGER PRIMARY KEY,
        name,
        comment,
        dummy);

create table LineItem(
        orderKey INTEGER,
        partKey INTEGER,
        suppKey INTEGER,
        lineNumber INTEGER,
        quantity INTEGER,
        extendedPrice REAL,
        discount REAL,
        tax REAL,
        returnFlag,
        lineStatus,
        shipDate DATETIME,
        commitDate DATETIME,
        receiptDate DATETIME,
        shipInstruct,
        shipMode,
        comment,
        dummy);

create table Orders(
        orderKey INTEGER PRIMARY KEY,
        custKey INTEGER,
        orderStatus,
        totalPrice REAL,
        orderDate DATETIME,
        orderPriority,
        clerk,
        shipPriority,
        comment,
        dummy);

create index SupplierNations on Supplier(nationkey);
create index CustomerNations on Customer(nationkey);
create index LineItemParts on LineItem(partkey);
create index LineItemSuppliers on LineItem(suppkey);
create index LineItemOrders on LineItem(orderkey);
create index OrderCustomers on Orders(custKey);
create index PartSuppSupp on PartSupp(suppkey);

create index OrderDate on Orders(orderDate);
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users