db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dan Karp <dk...@zimbra.com>
Subject IN clause and query plans
Date Sat, 17 Mar 2007 17:58:42 GMT
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


Mime
View raw message