EMAIL PROTECTED]]
Sent: Tuesday, January
17, 2006
7:25 PM
To:
derby-dev@db.apache.org
Subject: Re:
Optimizing subqueries
[ Was: Re: VTI, Indexed Lookup and the Query Optimizer ]
Hi Jeff,
Jeffrey Lichtman wrote:
Unfortunately,
I don't
remember why hash joins are prohibited in
created? Is
it created after one complete scan based on the query pattern?
From: Satheesh Bandaram [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 17, 2006
7:25 PM
To: derby-dev@db.apache.org
Subject: Re: Optimizing subqueries
[ Was: Re: VTI, Indexed Lookup and the Query Optimizer
Sorry - I somehow deleted part of my last message before sending it.
I'll try again:
The actual query that prompted this question, though, has a subquery
that uses aggregates and a GROUP BY--i.e. the subquery *cannot*, as
I understand it, be flattened into the outer query, because the
aggrega
Hi Jeff,
Jeffrey Lichtman wrote:
Unfortunately, I don't remember why hash joins are
prohibited in this case. One thing I notice in looking at the code is
the following in HashJoinStrategy.feasible():
Did you forget to cut_and_paste something from
HashJoinStrategy.feasible()? You said yo
The actual query that prompted this question, though, has a subquery
that uses aggregates and a GROUP BY--i.e. the subquery *cannot*, as
I understand it, be flattened into the outer query, because the
aggregate/group-by functionality has to be performed before
evaluation of the outer query ca
Jeffrey Lichtman wrote:
Based on logic in the code, the example query isn't flattenable. . .
That's because whoever wrote the code made it handle only the simplest
case. I doubt it would be hard to make it flatten many other types of
table subqueries.
The example I gave was a simplified s
Based on logic in the code, the example query isn't flattenable. . .
That's because whoever wrote the code made it handle only the
simplest case. I doubt it would be hard to make it flatten many other
types of table subqueries.
My general philosophy toward query performance issues is that
Jeffrey Lichtman wrote:
That said, it seems reasonable to think that the optimizer should at
least consider doing a hash join on the subquery, in which case the
join between T2 and T3 could be materialized and then a hash-join
could be done using the predicate x1.j = t1.i.
I think the best
That said, it seems reasonable to think that the optimizer should at
least consider doing a hash join on the subquery, in which case the
join between T2 and T3 could be materialized and then a hash-join
could be done using the predicate x1.j = t1.i.
I think the best thing would be to flatten
Satheesh Bandaram wrote:
Army wrote:
So if we have a subquery, in which case childResult will (or least can)
be a SelectNode, the fact that SelectNode is NOT an instance of Optimizable
means that this method will return false and the optimizer won't ever
consider a hash join when the inner table
Army wrote:
> This method is called from the "feasible()" methods of both
> NestedLoopJoinStrategy.java and HashJoinStrategy.java. In the latter
> case, if this method returns false, the optimizer won't ever try to do
> a hash join (at least, that's how I read the code). So if we have a
> subqu
Thanks, I will play with that!
-Original Message-
From: Satheesh Bandaram [mailto:[EMAIL PROTECTED]
Sent: Fri 1/13/2006 3:20 PM
To: derby-dev@db.apache.org
Subject: Re: VTI, Indexed Lookup and the Query Optimizer
Jeffrey Lichtman wrote:
> The optimizer should consider hash join o
Jeffrey Lichtman wrote:
> The optimizer should consider hash join on a VTI if it is the inner
> table of the join and it is materializable - that is, if the
> parameters to the constructor of the VTI are query-invariant. So, for
> example, if the params are all constants, hash join should be
> c
Jeffrey Lichtman wrote:
/**
* @see
OptimizerFactory#supportsOptimizerTrace
*/
public
boolean supportsOptimizerTrace()
{
return false;
}
It looks like it should be pretty easy to turn tracing on.
Right... I think this tra
Jeffrey Lichtman wrote:
Rather than try to get indexing to work on VTIs, I'd consider why the
query optimizer isn't using a hash join strategy, which would probably
give much better performance than the Cartesian product you're getting
now. The optimizer should consider hash join on a VTI if
e: VTI, Indexed Lookup and the Query Optimizer
>What can be done about this? It looks like FromVTI.java implements
>Optimizable, which I'm assuming is the interface to supply indexed
>lookup. But, like I said, my head is swimming trying to figure out
>if I either 1) don'
Is this what you were referring
to, Jeff?
derby.language.logQueryPlan
Thanks, but that's not it. logQueryPlan causes the final query plan to be
written to derby.log. I was thinking of a trace function that creates a
trail of all the decisions and calculations the optimizer makes in
derby.log. I
Is this what you were referring to, Jeff?
derby.language.logQueryPlan
Function
When this property is set to true, Derby writes the query plan information into the derby.log file for all executed queries.
This property is useful for debugging to know what query plan was chosen by the optimizer.
What can be done about this? It looks like FromVTI.java implements
Optimizable, which I'm assuming is the interface to supply indexed
lookup. But, like I said, my head is swimming trying to figure out
if I either 1) don't understand how to enable an indexed lookup for
my VTI or 2) if it's n
OK, my head is swimming trying to sort through all the
issues regarding query performance and VTI-based tables. We have
implemented several VTI tables that when joined together cause massive
performance problems.
I have narrowed down the issues I have to two areas:
The VTI “2” ve
20 matches
Mail list logo
