I ran an explain analyze on a similar query against a built-in function:
explain analyze select a from (select floor(x + 0.1) as a, floor(x - 0.1)
as b from system_range(0,100)) where b in (3,4,5,6,7)
How should I interpret the scan counts?
SELECT
A
FROM (
SELECT
FLOOR((X + 0.1)) AS A,
FLOOR((X - 0.1)) AS B
FROM SYSTEM_RANGE(0, 100)
/* PUBLIC.RANGE_INDEX */
) _0
/* SELECT
FLOOR((X + 0.1)) AS A,
FLOOR((X - 0.1)) AS B
FROM SYSTEM_RANGE(0, 100)
/++ PUBLIC.RANGE_INDEX ++/
/++ scanCount: 102 ++/
WHERE FLOOR((X - 0.1)) IS ?1: B IN(3, 4, 5, 6, 7)
*/
/* scanCount: 6 */
WHERE B IN(3, 4, 5, 6, 7)
Is that saying it scanned 108 rows total? Or is it 102*6?
On Friday, August 15, 2014 10:06:24 AM UTC-7, Brian Craft wrote:
>
> I'm doing a select like the following, with a user function B:
>
> SELECT B1 FROM (SELECT B(1, x) AS B1, B(2, x) AS B2 FROM system_range(0,
> 389)) WHERE (B2 IN (?,?,?...))
>
> B is getting called about 130k times. There are about 680 items in the IN
> clause. So, it gets called a number of times equal to half the product of
> the system_range and the IN clause.
>
> I naively thought the subselect would call B 389*2 times, then table scan
> the results. What is it actually doing?
>
--
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.
For more options, visit https://groups.google.com/d/optout.
