Bug report: Poor query performance

[Elad .]

Some parts of this message have been removed. Learn more about Nabble's security policy.

 
Hi,
 
I have a database with 52 columns and 7 million rows.
 
The columns are Date (timestamp data type), Customer (real data type) and a bunch of other columns that indicate values for each customer on each date (all of these columns are of real data type).
 
The 'Date' column contains 5 unique values.
 
The 'Customer' column contains 1.7 million unique values.
 
1. The following query returns instantly: SELECT "CUSTOMER" FROM "TABLE".
 
2. The following query takes a long time (stopped after a few minutes): SELECT "CUSTOMER" FROM "TABLE" GROUP BY "CUSTOMER".
 
3. The following query returns instantly: SELECT "DATE", "CUSTOMER" FROM "TABLE" GROUP BY "DATE", "CUSTOMER".
 
4. The following query takes a long time (stopped after a few minutes): SELECT "CUSTOMER", "DATE" from "TABLE" GROUP BY "CUSTOMER", "DATE".
 
* Note that the only difference between query 1 and query 2 are the addition of a group by clause to query 2.
 
* Note that the only difference between query 3 and query 4 are the order of columns in the select and group by clauses.
 
Is there a way to work around this?  I'm really stuck.
 
Thanks in advance,
 
Elad

---

Talk to your Yahoo! Friends via Windows Live Messenger. Find Out How
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Monetdb-developers mailing list
Monetdb-developers@...
https://lists.sourceforge.net/lists/listinfo/monetdb-developers

[Martin Kersten]

Elad . wrote:
>  
> Hi,
>  
> I have a database with 52 columns and 7 million rows.
dear Elad,

Thanks for using MonetDB. The problem you describe is known. No easy
solution exists,
because the front-end optimizer does not use, nor rely on table
statistics to derive the best
plan.
A solution will emerge, but don't hold your breath

regards, Martin

>  
> The columns are Date (timestamp data type), Customer (real data
> type) and a bunch of other columns that indicate values for each
> customer on each date (all of these columns are of real data type).
>  
> The 'Date' column contains 5 unique values.
>  
> The 'Customer' column contains 1.7 million unique values.
>  
> 1. The following query returns instantly: SELECT "CUSTOMER" FROM "TABLE".
>  
> 2. The following query takes a long time (stopped after a few
> minutes): SELECT "CUSTOMER" FROM "TABLE" GROUP BY "CUSTOMER".
>  
> 3. The following query returns instantly: SELECT "DATE", "CUSTOMER"
> FROM "TABLE" GROUP BY "DATE", "CUSTOMER".
>  
> 4. The following query takes a long time (stopped after a few
> minutes): SELECT "CUSTOMER", "DATE" from "TABLE" GROUP BY "CUSTOMER",
> "DATE".
>  
> * Note that the only difference between query 1 and query 2 are the
> addition of a group by clause to query 2.
>  
> * Note that the only difference between query 3 and query 4 are the
> order of columns in the select and group by clauses.
>  
> Is there a way to work around this?  I'm really stuck.
>  
> Thanks in advance,
>  
> Elad
>
> ------------------------------------------------------------------------
> Talk to your Yahoo! Friends via Windows Live Messenger. Find Out How
> <http://www.windowslive.com/explore/messenger?ocid=TXT_TAGLM_WL_messenger_yahoo_082008>
>
> ------------------------------------------------------------------------
>
> -------------------------------------------------------------------------
> This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
> Build the coolest Linux based applications with Moblin SDK & win great prizes
> Grand prize is a trip for two to an Open Source event anywhere in the world
> http://moblin-contest.org/redirect.php?banner_id=100&url=/
> ------------------------------------------------------------------------
>
> _______________________________________________
> Monetdb-developers mailing list
> Monetdb-developers@...
> https://lists.sourceforge.net/lists/listinfo/monetdb-developers
>  

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Monetdb-developers mailing list
Monetdb-developers@...
https://lists.sourceforge.net/lists/listinfo/monetdb-developers