Hi,
Thanks for your mail! It seems there is a bug in the conversion from "or"
to "in":
EXPLAIN ANALYZE SELECT * FROM SLAVE WHERE (MASTERID=1 OR MASTERID=3 OR
MASTERID=5 OR MASTERID=8 OR MASTERID=12);
PLAN
SELECT
SLAVE.ID,
SLAVE.NAME,
SLAVE.MASTERID
FROM PUBLIC.SLAVE
/* PUBLIC.SLAVE.tableScan */
/* scanCount: 20001 */
WHERE (MASTERID = 12)
OR ((MASTERID = 8)
OR ((MASTERID IN(1, 3))
OR (MASTERID = 5)))
The optimizer did partially convert the "or" to "in", but really only
partially (only 1 and 3). I hope we can fix this soon. Patches are welcome
of course!
Regards,
Thomas
On Wed, Apr 17, 2013 at 4:41 PM, davide.cavestro
<[email protected]>wrote:
> I've noticed a huge performance difference between a query with some
> conditions expressed through a IN clause and the corresponding OR
> conditions
> sequence.
> H2 uses uses indexes in the former, while it does a tableScan with the
> latter.
>
> Here you are a reproducible test case:
>
>
> where the explain analyze output is, respectively
>
>
>
> Please correct me if I'm wrong, but it seems that when I use the OR it does
> a tableScan, while with the IN it uses the index implicitly created with
> the
> FK.
> I've reproduced it with h2 v. 1.3.171.
> Is it a bug?
>
> Cheers
> Davide
>
>
>
> --
> View this message in context:
> http://h2-database.66688.n3.nabble.com/huge-performance-difference-between-SELECT-WHERE-x-IN-1-2-and-SELECT-WHERE-x-1-OR-x-2-tp4026210.html
> Sent from the H2 Database mailing list archive at Nabble.com.
>
> --
> You received this message because you are subscribed to the Google Groups
> "H2 Database" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/h2-database?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
