[
https://issues.apache.org/jira/browse/DERBY-7173?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17913020#comment-17913020
]
Richard Evans commented on DERBY-7173:
--------------------------------------
Here's some information from derby.log with derby.language.logQueryPlan=true
Tue Jan 14 18:50:09 GMT 2025 Thread[#1,main,5,main] (XID = 156530), (SESSIONID
= 5), SELECT * FROM test WHERE num = ? AND string = ? OR num = ? AND string = ?
******* Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 50000
Rows filtered = 49998
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: 25002.50
optimizer estimated cost: 14467.92
Source result set:
Table Scan ResultSet for TEST at read committed isolation level using
instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 50000
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 pages visited=483
Number of rows qualified=50000
Number of rows visited=50000
Scan type=heap
start position:
null
stop position:
null
qualifiers:
None
optimizer estimated row count: 25002.50
optimizer estimated cost: 14467.92
> Very poor performance with a = ? AND b = ? OR a = ? AND b = ? in query
> ----------------------------------------------------------------------
>
> Key: DERBY-7173
> URL: https://issues.apache.org/jira/browse/DERBY-7173
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.16.1.1
> Reporter: Richard Evans
> Priority: Major
> Attachments: lkp.java
>
>
> Execute a query like:
> SELECT * FROM table WHERE col1 = ? AND col2 = ? OR col1 = ? AND col2 = ?
> Performance is significantly worse then the simpler:
> SELECT * FROM table WHERE col1 = ? AND col2 = ?
> even though both col1 and col2 are indexed.
> Simple test case attached - run with db directory and number of OR clauses in
> query - use 2 for first example above and 1 for second.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
