Hi Mike, here's the query plan. It's pretty lengthy, sorry.
After I fixed the worst problem the performance difference between PostgreSQL and Derby is ca. 5x. In my table comparing PostgreSQL and Derby before after, Derby is flat at 200ms or so for each query. I *think* what's happening is that Derby has two large sets of records TREATMENT and FIELD. Derby needs to scan the entire main table to check if each main record TREATMENT is in the set of FIELD records. Hence the performance is roughly proportional to the # of main records and indeed 200ms seems to be like the 'standard' time to complete a query for all these queries now. There are 6311 TREATMENT records and 322 FIELD records. > If you are able to post a query plan for that query I would be willing to look > at it to see if derby is doing a scan or if the optimizer is using the expected > "multi-probe" join strategy. Thu Apr 16 07:53:10 CEST 2015 Thread[qtp1419014327-22,5,main] (XID = 11827028), (SESSIONID = 1), SELECT DISTINCT t0.TREAT_CORRECTIVEDESC, t0.TREAT_NO, t0.TREAT_INCIDID, t0.TREAT_ENTEREDBYCONTACT, t0.TREAT_CRITID, t0.treat_treatcontact, t0.TREAT_ENTEREDBY, t0.TREAT_ID, t0.TREAT_REFINT, t0.TREAT_REPORTEDDATE, t0.TREAT_UPDATEDDATE, t0.TREAT_DESC, t0.TREAT_SOURCETYPE, t0.TREAT_QUALITYTYPE, t0.causedSysUser, t0.areaResponsible, t0.TREAT_PROID, t0.TREAT_TREATASPECID, t0.TREAT_TREATROLE, t0.TREAT_TREATUSER, t0.TREAT_PARENTID, t0.TREAT_SYSORGUNITID, t0.sev_id, t0.TREAT_LIMITDATE, t0.TREAT_RESPONSIBLECONTACT, t0.TREAT_ACTIONDESC, t0.TREAT_ELCSTATUSID, t0.TREAT_REPORTEDBYCONPER, t0.responsibleSysUser, t0.freq_id, t0.TREAT_ENTEREDDATE, t0.TREAT_REPORTEDBYCONTACT, t0.TREAT_TREATCSPECID, t0.treat_causedcontact FROM TREATMENT t0 JOIN ELC_STATUS t1 ON (t0.TREAT_ELCSTATUSID = t1.ELCSTATUS_ID) JOIN fieldlink t2 ON (t0.TREAT_ID = t2.treatment) JOIN field t3 ON (t2.field = t3.id) JOIN fieldchildren t4 ON (t3.id = t4.child) WHERE (t0.TREAT_REPORTEDDATE IS NOT NULL) AND (t0.TREAT_NO IS NOT NULL) AND (t1.ELCSTATUS_PROGVALUE <> ?) AND (t0.TREAT_REPORTEDDATE IS NOT NULL) AND (t0.TREAT_NO IS NOT NULL) AND (t2.customfield = ?) AND (t4.parent = ?) ******* Sort ResultSet: Number of opens = 1 Rows input = 1292 Rows returned = 1292 Eliminate duplicates = true In sorted order = false Sort information: Number of merge runs=1 Number of rows input=1292 Number of rows output=1292 Size of merge runs=[1279] Sort type=external constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 11.59 optimizer estimated cost: 1006.81 Source result set: Project-Restrict ResultSet (15): Number of opens = 1 Rows seen = 1292 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 11.59 optimizer estimated cost: 1006.81 Source result set: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 4995 Rows seen from the right = 1292 Rows filtered = 0 Rows returned = 1292 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 11.59 optimizer estimated cost: 1006.81 Left result set: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 4995 Rows seen from the right = 4995 Rows filtered = 0 Rows returned = 4995 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 11.59 optimizer estimated cost: 952.18 Left result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 4995 Rows seen from the right = 4995 Rows filtered = 0 Rows returned = 4995 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 11.59 optimizer estimated cost: 915.75 Left result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 6 Rows seen from the right = 4995 Rows filtered = 0 Rows returned = 4995 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 24.06 optimizer estimated cost: 397.91 Left result set: Table Scan ResultSet for ELC_STATUS at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 6 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={3, 4} Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=6 Number of rows visited=7 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 4 Operator: = Ordered nulls: false Unknown return value: true Negate comparison result: true optimizer estimated row count: 3.96 optimizer estimated cost: 30.90 Right result set: Project-Restrict ResultSet (9): Number of opens = 6 Rows seen = 4999 Rows filtered = 4 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 24.06 optimizer estimated cost: 367.01 Source result set: Index Row to Base Row ResultSet for TREATMENT: Number of opens = 6 Rows seen = 4999 Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 24.06 optimizer estimated cost: 367.01 Index Scan ResultSet for TREATMENT using constraint SQL120518171026210 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 6 Rows seen = 4999 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=179 Number of pages visited=102 Number of rows qualified=4999 Number of rows visited=5184 Scan type=btree Tree height=3 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None optimizer estimated row count: 24.06 optimizer estimated cost: 367.01 Right result set: Project-Restrict ResultSet (12): Number of opens = 4995 Rows seen = 30629 Rows filtered = 25634 restriction = true projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 11.59 optimizer estimated cost: 517.84 Source result set: Index Row to Base Row ResultSet for FIELDLINK: Number of opens = 4995 Rows seen = 30629 Columns accessed from heap = {1, 3, 10} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 11.59 optimizer estimated cost: 517.84 Index Scan ResultSet for FIELDLINK using constraint SQL120518171122050 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 4995 Rows seen = 30629 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=2 Number of deleted rows visited=5029 Number of pages visited=15904 Number of rows qualified=30629 Number of rows visited=40653 Scan type=btree Tree height=3 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None optimizer estimated row count: 11.59 optimizer estimated cost: 517.84 Right result set: Index Scan ResultSet for FIELD using constraint SQL100212164533470 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 4995 Rows seen = 4995 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=9990 Number of rows qualified=4995 Number of rows visited=4995 Scan type=btree Tree height=2 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None optimizer estimated row count: 11.59 optimizer estimated cost: 36.43 Right result set: Index Scan ResultSet for FIELDCHILDREN using constraint SQL150416002622780 at read committed isolation level using share row locking chosen by the optimizer Number of opens = 4995 Rows seen = 1292 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={0, 1} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=15082 Number of rows qualified=1292 Number of rows visited=4995 Scan type=btree Tree height=3 start position: >= on first 2 column(s). Ordered null semantics on the following columns: 0 stop position: > on first 2 column(s). Ordered null semantics on the following columns: 0 qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false optimizer estimated row count: 11.59 optimizer estimated cost: 54.64 -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/