db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: performance, memory consumption...and a big hello :)
Date Wed, 29 Jul 2009 13:37:25 GMT
Tomi N/A wrote:
> Thank you both for the comments.
> Jan, I've kind of taken your advice even before I saw it :), but I
> tried PostgreSQL, unfortunately, with no obvious improvement.
> 2009/7/27 Rick Hillegas <Richard.Hillegas@sun.com>:
>>> select id_group, count(id)
>>> group by id_group
>> Not to be tiresome, but just to make sure we understand the query: I think
>> you mean id_group1 instead of id_group.
> Exactly right.
>> It sounds as though you may have created an index on ( id_group1, id ). At
>> first blush, I would expect that index to improve the performance. Did that
>> not happen?
> That is exactly what I observed.
> As I told Jan, I've tried it out on PostgreSQL in the meantime because
> a) I wanted to see how a different RDBMS would behave and b) because
> derby (it seems) doesn't support the "explain" command to show the
> query plan and PostgreSQL does.

Hello Tomislav,

You might want to read this: 
Especially, follow the link for the QueryPlanJoinOrder.

This feature of Derby isn't what I would call the most user friendly, 
but with some effort it should give you the information you need.

A new feature has also been introduced into the development trunk, 
called the XPLAIN style runtime statistics. It may or may not be of 
interest to you, consult the Derby Tuning Guide for more information.


> I've tried the following indexes:
> (id)
> (id_group1)
> (id, id_group1)
> but postgresql announced a sequential table scan every time and, in
> fact, seemed to have done exactly that when I executed the query.
> Either I'm doing something obviously wrong or am assuming something
> obviously impossible is possible, but I'm inclined to believe the
> former, rather than the latter which is why I'm writing. :-)
> Cheers,
> Tomislav

View raw message