I'm seeing some slowness in a query that should be using an index, and I'm
wondering if it's because Derby's query optimizer hates my use of the IN
clause. I'm using 10.2.2 embedded.
Here's the query and plan with the 78-element IN clause:
Executing prepared statement: SELECT parent_id, id, unread, flags, tags FROM
mboxgroup1.mail_item WHERE mailbox_id = ? AND parent_id IN (?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ORDER BY parent_id
Number of opens = 1
Rows seen = 447
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: 42049.50
optimizer estimated cost: 10.02
Source result set:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 447
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: 42049.50
optimizer estimated cost: 10.02
Source result set:
Index Row to Base Row ResultSet for MAIL_ITEM:
Number of opens = 1
Rows seen = 25705
Columns accessed from heap = {0, 1, 3, 11, 12, 13}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 42049.50
optimizer estimated cost: 10.02
Index Scan ResultSet for MAIL_ITEM using constraint
FK_MAIL_ITEM_PARENT_ID at read committed isolation level using share row
locking chosen by the optimizer
Number of opens = 1
Rows seen = 25705
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=69
Number of rows qualified=25705
Number of rows visited=25706
Scan type=btree
Tree height=2
start position:
>= on first 2 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 2 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 42049.50
optimizer estimated cost: 10.02
When I remove the IN clause and just issue 78 separate queries, things look
much better:
Executing prepared statement: SELECT parent_id, id, unread, flags, tags FROM
mboxgroup1.mail_item WHERE mailbox_id = ? AND parent_id IN (?) ORDER BY
parent_id
Number of opens = 1
Rows seen = 9
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.83
optimizer estimated cost: 10.02
Source result set:
Index Row to Base Row ResultSet for MAIL_ITEM:
Number of opens = 1
Rows seen = 9
Columns accessed from heap = {0, 1, 3, 11, 12, 13}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 1.83
optimizer estimated cost: 10.02
Index Scan ResultSet for MAIL_ITEM using constraint
FK_MAIL_ITEM_PARENT_ID at read committed isolation level using share row
locking chosen by the optimizer
Number of opens = 1
Rows seen = 9
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=2
Number of rows qualified=9
Number of rows visited=10
Scan type=btree
Tree height=2
start position:
>= on first 2 column(s).
Ordered null semantics on the following columns:
stop position:
> on first 2 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 1.83
optimizer estimated cost: 10.02
