db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Index not used for complex WHERE
Date Mon, 15 Sep 2008 13:16:10 GMT
Jan Kotek <opencoeli@gmail.com> writes:

> Hello,
>
> it seems to me that Derby is not using index for more complex WHERE
> cause.
>
> Table have only 3 colums, all number. There is only one index on IPIX
> column (BIGINT). There is ~100000 records in table.
>
> With simple query:
> SELECT obj FROM MilkyWayPixel obj 
>   WHERE  (obj.ipix BETWEEN 15284 AND 33739) 
>
> result is returned immediately (0.1s).  But with more complex query it
> takes around 3 seconds. This time also grows with number of records. And
> it did not change if there is index on IPIX or not. Derby is clearly not
> using index in this case. Instead it makes full table scan.

Derby's optimizer is known to make bad decisions in some cases when the
index cardinality statistics are outdated. There is some work in
progress to update the statistics automatically, but until that's
finished, you'll have to update them manually either by dropping the
index and recreating it after the table has been populated, or by
compressing the table (with the SYSCS_UTIL.SYSCS_COMPRESS_TABLE
procedure, see
http://db.apache.org/derby/docs/10.4/ref/rrefaltertablecompress.html).

If these workarounds don't help, it would be very helpful if you could
file a bug report at https://issues.apache.org/jira/browse/DERBY and
upload some test data and SQL statements that show the problem.

> Is there any way how I can optimalize query?

You can force the optimizer to pick a plan which uses the index. See
this section in Tuning Derby for an example:
http://db.apache.org/derby/docs/10.4/tuning/ctundepthoptover.html

-- 
Knut Anders

Mime
View raw message