Tony Brusseau created DERBY-6132:
------------------------------------
Summary: FETCH NEXT extremely slow
Key: DERBY-6132
URL: https://issues.apache.org/jira/browse/DERBY-6132
Project: Derby
Issue Type: Improvement
Components: Store
Affects Versions: 10.9.1.0
Environment: Debian Linux
Reporter: Tony Brusseau
I tried an expensive count query that counted almost 300k rows from a table
with > 15 million rows. It took 5.8 seconds. I then tried optimizing the count
query to only fetch the first 129 results (because I only care if there are
more than 128 results) and the query took 5.9 seconds...even longer! In both
cases the query plan finds all 300k rows before returning results which
severely limits the utility of the FETCH NEXT command.
SELECT COUNT(DISTINCT a.term_id) FROM kb.gaf_assertion_term a,
kb.formula_entries fe
WHERE (fe.formula_id = a.formula_id) AND (fe.arg_term = 1407374883553721) AND
(fe.formula_type = 1)
AND (fe.arg_num > -1)
Execution finished after 5.787 s, 0 error(s) occurred.
284960 result
SELECT COUNT(*) FROM (SELECT DISTINCT a.term_id FROM kb.gaf_assertion_term a,
kb.formula_entries fe
WHERE (fe.formula_id = a.formula_id) AND (fe.arg_term = 1407374883553721) AND
(fe.formula_type = 1)
AND (fe.arg_num > -1) FETCH NEXT 129 ROWS ONLY) x
Execution finished after 5.855 s, 0 error(s) occurred.
129 result
*******************************************************************************************
QUERY PLAN: WITH FETCH NEXT
Fri Mar 29 17:30:28 CDT 2013 Thread[DRDAConnThread_3,5,main] (XID = 76255884),
(SESSIONID = 1), SELECT COUNT(*) FROM (SELECT DISTINCT a.term_id FROM
kb.gaf_assertion_term a, kb.formula_entries fe
WHERE (fe.formula_id = a.formula_id) AND (fe.arg_term = 1407374883553721) AND
(fe.formula_type = 1)
AND (fe.arg_num > -1) FETCH NEXT 129 ROWS ONLY) x ******* Project-Restrict
ResultSet (12):
Number of opens = 1
Rows seen = 1
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: 1.00
optimizer estimated cost: 1205122.64
Source result set:
Scalar Aggregate ResultSet:
Number of opens = 1
Rows input = 129
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 91812.27
optimizer estimated cost: 1205122.64
Index Key Optimization = false
Source result set:
Project-Restrict ResultSet (11):
Number of opens = 1
Rows seen = 129
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: 91812.27
optimizer estimated cost: 1205122.64
Source result set:
Row Count (2):
Number of opens = 1
Rows seen = 129
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 91812.27
optimizer estimated cost: 1205122.64
Source result set:
Sort ResultSet:
Number of opens = 1
Rows input = 284973
Rows returned = 129
Eliminate duplicates = true
In sorted order = false
Sort information:
Number of rows input=284973
Number of rows output=284960
Sort type=internal
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 97588.77
optimizer estimated cost: 1205122.64
Source result set:
Project-Restrict ResultSet (10):
Number of opens = 1
Rows seen = 284973
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:
97588.77
optimizer estimated cost:
1205122.64
Source result set:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 284688
Rows seen from the right =
284973
Rows filtered = 0
Rows returned = 284973
constructor time
(milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
optimizer estimated row
count: 97588.77
optimizer estimated
cost: 1205122.64
Left result set:
Project-Restrict
ResultSet (7):
Number of opens = 1
Rows seen = 284688
Rows filtered = 0
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: 91812.27
optimizer
estimated cost: 554694.34
Source result set:
Index Row to
Base Row ResultSet for FORMULA_ENTRIES:
Number of opens
= 1
Rows seen =
284688
Columns
accessed from heap = {1, 4}
constructor time (milliseconds) = 0
open
time (milliseconds) = 0
next
time (milliseconds) = 0
close
time (milliseconds) = 0
optimizer estimated row count: 91812.27
optimizer estimated cost: 554694.34
Index
Scan ResultSet for FORMULA_ENTRIES using index
KB_FORMULA_ENTRIES_FORMULA_TERM_TYPE at read committed isolation level using
share row locking chosen by the optimizer
Number
of opens = 1
Rows
seen = 284688
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=All
Number of columns fetched=3
Number of deleted rows visited=0
Number of pages visited=277
Number of rows qualified=284688
Number of rows visited=284689
Scan type=btree
Tree height=3
start position:
>= on first 2 column(s).
Ordered null semantics on the following columns:
0 1
stop position:
> on first 2 column(s).
Ordered null semantics on the following columns:
0 1
qualifiers:
None
optimizer estimated row count: 91812.27
optimizer estimated cost: 554694.34
Right result set:
Index Row to Base Row
ResultSet for GAF_ASSERTION_TERM:
Number of opens = 284688
Rows seen = 284973
Columns accessed from
heap = {0}
constructor
time (milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
optimizer
estimated row count: 97588.77
optimizer
estimated cost: 650428.30
Index Scan
ResultSet for GAF_ASSERTION_TERM using constraint
KB_GAF_ASSERTION_TERM_FORMULA_MT_UNIQUE at read committed isolation level using
share row locking chosen by the optimizer
Number of opens
= 284688
Rows seen =
284973
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, 2}
Number
of columns fetched=2
Number
of deleted rows visited=0
Number
of pages visited=854700
Number
of rows qualified=284973
Number
of rows visited=569661
Scan
type=btree
Tree
height=3
start
position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
0
stop
position:
> on first 1 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 97588.77
optimizer estimated cost: 650428.30
*******************************************************************************************
QUERY PLAN: WITHOUT FETCH NEXT
Fri Mar 29 17:28:15 CDT 2013 Thread[DRDAConnThread_3,5,main] (XID = 76255878),
(SESSIONID = 1), SELECT COUNT(DISTINCT a.term_id) FROM kb.gaf_assertion_term a,
kb.formula_entries fe
WHERE (fe.formula_id = a.formula_id) AND (fe.arg_term = 1407374883553721) AND
(fe.formula_type = 1)
AND (fe.arg_num > -1) ******* Project-Restrict ResultSet (9):
Number of opens = 1
Rows seen = 1
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: 1.00
optimizer estimated cost: 1205122.64
Source result set:
Distinct Scalar Aggregate ResultSet:
Number of opens = 1
Rows input = 284973
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 97588.77
optimizer estimated cost: 1205122.64
Source result set:
Project-Restrict ResultSet (8):
Number of opens = 1
Rows seen = 284973
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: 97588.77
optimizer estimated cost: 1205122.64
Source result set:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 284688
Rows seen from the right = 284973
Rows filtered = 0
Rows returned = 284973
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 97588.77
optimizer estimated cost: 1205122.64
Left result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 284688
Rows filtered = 0
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: 91812.27
optimizer estimated cost: 554694.34
Source result set:
Index Row to Base Row ResultSet for
FORMULA_ENTRIES:
Number of opens = 1
Rows seen = 284688
Columns accessed from heap = {1, 3, 4,
5}
constructor time (milliseconds)
= 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count:
91812.27
optimizer estimated cost:
554694.34
Index Scan ResultSet for
FORMULA_ENTRIES using index KB_FORMULA_ENTRIES_FORMULA_TERM_TYPE at read
committed isolation level using instantaneous share row locking chosen by the
optimizer
Number of opens = 1
Rows seen = 284688
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=3
Number of deleted rows
visited=0
Number of pages
visited=277
Number of rows
qualified=284688
Number of rows
visited=284689
Scan type=btree
Tree height=3
start position:
>= on first 2
column(s).
Ordered null
semantics on the following columns:
0 1
stop position:
> on first 2
column(s).
Ordered null
semantics on the following columns:
0 1
qualifiers:
None
optimizer estimated row
count: 91812.27
optimizer estimated
cost: 554694.34
Right result set:
Index Row to Base Row ResultSet for
GAF_ASSERTION_TERM:
Number of opens = 284688
Rows seen = 284973
Columns accessed from heap = {0, 6}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 97588.77
optimizer estimated cost: 650428.30
Index Scan ResultSet for
GAF_ASSERTION_TERM using constraint KB_GAF_ASSERTION_TERM_FORMULA_MT_UNIQUE at
read committed isolation level using instantaneous share row locking chosen by
the optimizer
Number of opens = 284688
Rows seen = 284973
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={0,
2}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=854700
Number of rows qualified=284973
Number of rows visited=569661
Scan type=btree
Tree height=3
start position:
>= on first 1 column(s).
Ordered null semantics
on the following columns:
0
stop position:
> on first 1 column(s).
Ordered null semantics
on the following columns:
0
qualifiers:
None
optimizer estimated row count:
97588.77
optimizer estimated cost:
650428.30
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
