db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Pendleton <bpendle...@amberpoint.com>
Subject Re: optimizer seems to get confused by trivial table views
Date Mon, 30 Nov 2009 01:40:27 GMT
> do you think this is a bug? or am i doing something incorrectly?

I think the behavior you are seeing is a bug, and should be addressed.

It's clear that the query plans are different, and that the one
query plan is dramatically inferior, as it chooses a table scan rather
than using the available index. The table scan requires touching all
350,000 rows in the table, and thus your performance difference is dramatic.

I don't know what is causing this, but certainly the optimizer
has some very delicate handling of the predicates (s.ticker='OIH' and
o.fd >= '20081001', in your case) as it is manipulating the various
tables involved in the query, and it could well be that the optimizer
is failing to "push down" the predicate through the view, causing it
to fail to recognize that the index could be used effectively in the query.

Can you package up your findings and open an issue in JIRA?

If you can provide a complete standalone test program, including the
sample data, that would be great, but if you could at least start with
the information that you've already provided (your SQL.TXT file and your
derby.log file), that would be a great start.



View raw message