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/
