db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Tony Brusseau (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DERBY-6045) Can't bulk load terms by primary key efficiently
Date Thu, 17 Jan 2013 18:00:28 GMT
Tony Brusseau created DERBY-6045:
------------------------------------

             Summary: Can't bulk load terms by primary key efficiently
                 Key: DERBY-6045
                 URL: https://issues.apache.org/jira/browse/DERBY-6045
             Project: Derby
          Issue Type: Bug
          Components: Store
    Affects Versions: 10.9.1.0
         Environment: Linux Debian 6.0.5
            Reporter: Tony Brusseau
            Priority: Critical


I have a table with a long integer primary key field and 11 million rows. I seem to be unable
to load large chunks of rows via id in a reasonably efficient manner.

  1. If I do individual lookups via the primary key, then a fast indexed lookup occurs. However,
if I do large numbers of such queries, then the time is overwhelmed by round-trip overhead
which makes everything incredibly slow.

  2. If I use a single query with a disjunction of the primary keys of interest,  then a table
scan is performed (even if the clause only contest 1-3 items), which walks over 11 million
rows...incredibly inefficient.

  3. If I use an IN clause, then a table scan is performed (even if the clause only contest
1-3 items), which walks over 11 million rows...incredibly inefficient.

Here are the unlimited query plans for the 3 cases that I enumerated:
*********************************************************************************************

[EL Fine]: 2013-01-17 11:09:53.384--ServerSession(582235416)--Connection(1430986883)--Thread(Thread["Initial
Lisp Listener",5,SubL Thread Group])--SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH,
FORMULA_LENGTH, FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (TERM_ID = ?)
	bind => [2251799814033500]

Thu Jan 17 11:09:53 CST 2013 Thread["Initial Lisp Listener",5,SubL Thread Group] (XID = 4711079),
(SESSIONID = 3), SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH, FORMULA_LENGTH, FORMULA_TYPE,
KB_STATUS FROM KB.FORMULA_TERM WHERE (TERM_ID = ?) ******* Project-Restrict ResultSet (3):
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: 6.59
Source result set:
	Index Row to Base Row ResultSet for FORMULA_TERM:
	Number of opens = 1
	Rows seen = 1
	Columns accessed from heap = {1, 2, 3, 4, 5, 6, 7, 8}
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		optimizer estimated row count: 1.00
		optimizer estimated cost: 6.59
		Index Scan ResultSet for FORMULA_TERM using constraint KB_FORMULA_TERM_TERM_ID_PK at read
committed isolation level using share row locking chosen by the optimizer
		Number of opens = 1
		Rows seen = 1
		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=2
			Number of deleted rows visited=0
			Number of pages visited=3
			Number of rows qualified=1
			Number of rows visited=1
			Scan type=btree
			Tree height=-1
			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: 1.00
			optimizer estimated cost: 6.59

[EL Fine]: 2013-01-17 11:01:00.732--ServerSession(1237006689)--Connection(927179828)--Thread(Thread["Initial
Lisp Listener",5,SubL Thread Group])--SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH,
FORMULA_LENGTH, FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (((TERM_ID = ?) OR (TERM_ID
= ?)) OR (TERM_ID = ?))
	bind => [2251799814033500, 2251799814033501, 2251799814033499]

Thu Jan 17 11:01:10 CST 2013 Thread["Initial Lisp Listener",5,SubL Thread Group] (XID = 4711078),
(SESSIONID = 3), SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH, FORMULA_LENGTH, FORMULA_TYPE,
KB_STATUS FROM KB.FORMULA_TERM WHERE (((TERM_ID = ?) OR (TERM_ID = ?)) OR (TERM_ID = ?)) *******
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 3
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: 1176730.30
	optimizer estimated cost: 5931065.54
Source result set:
	Project-Restrict ResultSet (2):
	Number of opens = 1
	Rows seen = 11767298
	Rows filtered = 11767295
	restriction = true
	projection = false
		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: 1176730.30
		optimizer estimated cost: 5931065.54
	Source result set:
		Table Scan ResultSet for FORMULA_TERM at read committed isolation level using instantaneous
share row locking chosen by the optimizer
		Number of opens = 1
		Rows seen = 11767298
		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=9
			Number of pages visited=34358
			Number of rows qualified=11767298
			Number of rows visited=11767298
			Scan type=heap
			start position:
				null
			stop position:
				null
			qualifiers:
				None
			optimizer estimated row count: 1176730.30
			optimizer estimated cost: 5931065.54

[EL Fine]: 2013-01-17 11:27:00.627--ServerSession(1237006689)--Connection(1688096771)--Thread(Thread["Initial
Lisp Listener",5,SubL Thread Group])--SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH,
FORMULA_LENGTH, FORMULA_TYPE, KB_STATUS FROM KB.FORMULA_TERM WHERE (TERM_ID IN (?,?,?))
	bind => [2251799814033500, 2251799814033501, 2251799814033499]

Thu Jan 17 11:47:26 CST 2013 Thread["Initial Lisp Listener",5,SubL Thread Group] (XID = 4711080),
(SESSIONID = 3), SELECT TERM_ID, ARG0, ARG1, ARG2, ARG3, FORMULA_HASH, FORMULA_LENGTH, FORMULA_TYPE,
KB_STATUS FROM KB.FORMULA_TERM WHERE (TERM_ID IN (?,?,?)) ******* Project-Restrict ResultSet
(3):
Number of opens = 1
Rows seen = 3
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: 1176730.30
	optimizer estimated cost: 5931065.54
Source result set:
	Project-Restrict ResultSet (2):
	Number of opens = 1
	Rows seen = 11767298
	Rows filtered = 11767295
	restriction = true
	projection = false
		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: 1176730.30
		optimizer estimated cost: 5931065.54
	Source result set:
		Table Scan ResultSet for FORMULA_TERM at read committed isolation level using instantaneous
share row locking chosen by the optimizer
		Number of opens = 1
		Rows seen = 11767298
		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=9
			Number of pages visited=34358
			Number of rows qualified=11767298
			Number of rows visited=11767298
			Scan type=heap
			start position:
				null
			stop position:
				null
			qualifiers:
				None
			optimizer estimated row count: 1176730.30
			optimizer estimated cost: 5931065.54


--
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

Mime
View raw message