I am running the following query with Derby. All columns are indexed and of type varchar. ID is a primary key.
SELECT distinct event.ID, people.ID
FROM pstore AS event, pstore AS people, queries as q, queries as q2, xindex as x, xindex as x2
WHERE event.ID = 100393 AND
people.TYPEURI = 'http://www.research.ibm.com/maricopav2/wefinance/people' AND
q.xpath = '/fn:filenetEvent/fn:actor/text()' AND
q.queryid = x.queryid AND
x.result = 'lisa' AND
x.pstoreid = event.appID AND
x2.result = x.result AND
q2.xpath = '/p:employee/fn:actor/text()' AND
q2.queryid = x2.queryid AND
x2.pstoreid = people.appID
Already at a few hundred rows for the tables pstore and xindex (table queries is very small), the performance is terribly low.
Running the same query with the exact same indexes and data on DB2 gives good running times results, even for much larger tables.
I'll be happy to hear any advice regarding how to make the query run faster on Derby.