Hi all,
I have the following DDL and query; please note the query is written
this way to direct proper use of the index; it is much faster than any
JOIN-based alternative we've tried (and we've tried quite a few). The
problem is only with sorting the outer SELECT; while we appreciate any
suggestion to rewrite the nested queries in a more readable manner, that
is not really the issue unless there is some connection we can't grasp.
Some metrics: the table is about 2M records and the estimates in the
attached execution plan are quite accurate.
The problem:
The size of the outer SELECT result set is ~20K rows. If we take away
the outer ORDER BY clause the query runs in <10ms. Add the outer ORDER
BY clause and it takes 76 seconds !!! It seems that a simple sorting of
a 20K result set which should not be bigger than ~300KB binary takes 76
seconds!!! Moreover, profiling with different result sizes it becomes
evident that the time is linear to N^2. It looks almost like the whole
query is re-run for each result row to populate the next-lowest in some
weird form of selection sort, from what I can tell from the execution plan.
Question:
Is this expected behavior? The EXPLAIN QUERY PLAN says a temp b-tree is
used for sorting the outer select, but it also seems to double the inner
loops which is quite consistent with the N^2 behavoiur exhibited; how
can we improve this to be able to have SORT BY ... LIMIT on this SQL?
(reading the whole result set and sorting externally takes <2ms but it's
obviously suboptimal)?
Please note, we've already tried:
CREATE TEMP TABLE x AS (...query without outer sort...); CREATE xind ON
x (price); SELECT * FROM x ORDER BY 2
but it also takes 30 seconds; for some really esoteric reason, CREATE
TABLE x AS ... takes all of those 30s, even though the execution plan
looks identical to the simple select (the same 72 steps). This is what I
would dare call "spooky", any explanation would be appreciated here too.
Any help would be greatly appreciated, it's a delicate point in our
project.
Thanks and sorry for the excruciatingly long mail,
Dinu
Environment:
SqLite 3.7.17 / CentOS 7 (vanilla installled from repos via yum)
File format 4
PRAGMA automatic_index : 1
DDL:
CREATE TABLE materializedPrices (
id INTEGER,
price FLOAT,
perPerson FLOAT,
currency TEXT,
promotionType INTEGER,
promotionName TEXT,
provider TEXT,
instanceId TEXT,
accomodationHash TEXT,
accomodationName TEXT,
start DATE,
[end] DATE,
PRIMARY KEY ( id )
);
CREATE INDEX ffd0d22435eccb1020821f3ec70d038dfc7e5d91 ON
materializedPrices (provider,instanceId,start,end,accomodationHash,price);
QUERY:
SELECT
s.___packageId,
(
SELECT
(
SELECT
(
SELECT
MIN(m.price)
FROM
materializedPrices AS m
WHERE
m.provider=__provider AND
m.instanceId=__instanceId AND
m.start<='2015-03-08' AND
m.end>='2015-03-08' AND
m.accomodationHash IN ('2a')
) +
(
SELECT
MIN(m.price)
FROM
materializedPrices AS m
WHERE
m.provider=__provider AND
m.instanceId=__instanceId AND
m.start<='2015-03-08' AND
m.end>='2015-03-08' AND
m.accomodationHash IN ('2a')
) AS price
FROM (
SELECT DISTINCT
date AS _date,
duration AS _duration,
transportType AS _transportType
FROM
materializedInstances
WHERE
packageId=___packageId AND
provider=__provider AND
instanceId=__instanceId
)
WHERE
price IS NOT NULL
ORDER BY
1
LIMIT 1
) AS price
FROM (
SELECT DISTINCT
provider AS __provider,
instanceId AS __instanceId
FROM materializedInstances
WHERE
packageId=___packageId
)
WHERE
price IS NOT NULL
ORDER BY
1
LIMIT 1
) AS price
FROM (
SELECT DISTINCT
packageId AS ___packageId
FROM
materializedInstances
) AS s
/* Toggle below sort to exhibit weird behavior */
ORDER BY 2
Execution plan with sort:
| |.|.|.
---+--+-+-+--------------------------------------------------------------------------------------------------------------------------------------------------------
1 |1 |0|0|SCAN TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (~20525 rows)
2 |0 |0|0|SCAN SUBQUERY 1 AS s (~20525 rows)
3 |0 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
4 |3 |0|0|SEARCH TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (packageId=?) (~19 rows)
5 |2 |0|0|SCAN SUBQUERY 3 (~9 rows)
6 |2 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
7 |5 |0|0|SEARCH TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (packageId=? AND provider=? AND
instanceId=?) (~1 rows)
8 |4 |0|0|SCAN SUBQUERY 5 (~1 rows)
9 |4 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
10 |6 |0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
11 |6 |0|0|EXECUTE LIST SUBQUERY 7
12 |4 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 7
13 |7 |0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
14 |7 |0|0|EXECUTE LIST SUBQUERY 8
15 |4 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 8
16 |8 |0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
17 |8 |0|0|EXECUTE LIST SUBQUERY 9
18 |4 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 9
19 |9 |0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
20 |9 |0|0|EXECUTE LIST SUBQUERY 10
21 |4 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 10
22 |10|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
23 |10|0|0|EXECUTE LIST SUBQUERY 11
24 |4 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 11
25 |11|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
26 |11|0|0|EXECUTE LIST SUBQUERY 12
27 |4 |0|0|USE TEMP B-TREE FOR ORDER BY
28 |2 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 12
29 |13|0|0|SEARCH TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (packageId=? AND provider=? AND
instanceId=?) (~1 rows)
30 |12|0|0|SCAN SUBQUERY 13 (~1 rows)
31 |12|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 14
32 |14|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
33 |14|0|0|EXECUTE LIST SUBQUERY 15
34 |12|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 15
35 |15|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
36 |15|0|0|EXECUTE LIST SUBQUERY 16
37 |12|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 16
38 |16|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
39 |16|0|0|EXECUTE LIST SUBQUERY 17
40 |12|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 17
41 |17|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
42 |17|0|0|EXECUTE LIST SUBQUERY 18
43 |12|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 18
44 |18|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
45 |18|0|0|EXECUTE LIST SUBQUERY 19
46 |12|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 19
47 |19|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
48 |19|0|0|EXECUTE LIST SUBQUERY 20
49 |12|0|0|USE TEMP B-TREE FOR ORDER BY
50 |2 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 20
51 |21|0|0|SEARCH TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (packageId=? AND provider=? AND
instanceId=?) (~1 rows)
52 |20|0|0|SCAN SUBQUERY 21 (~1 rows)
53 |20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 22
54 |22|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
55 |22|0|0|EXECUTE LIST SUBQUERY 23
56 |20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 23
57 |23|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
58 |23|0|0|EXECUTE LIST SUBQUERY 24
59 |20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 24
60 |24|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
61 |24|0|0|EXECUTE LIST SUBQUERY 25
62 |20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 25
63 |25|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
64 |25|0|0|EXECUTE LIST SUBQUERY 26
65 |20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 26
66 |26|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
67 |26|0|0|EXECUTE LIST SUBQUERY 27
68 |20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 27
69 |27|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
70 |27|0|0|EXECUTE LIST SUBQUERY 28
71 |20|0|0|USE TEMP B-TREE FOR ORDER BY
72 |2 |0|0|USE TEMP B-TREE FOR ORDER BY
73 |0 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 28
74 |29|0|0|SEARCH TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (packageId=?) (~19 rows)
75 |28|0|0|SCAN SUBQUERY 29 (~9 rows)
76 |28|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 30
77 |31|0|0|SEARCH TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (packageId=? AND provider=? AND
instanceId=?) (~1 rows)
78 |30|0|0|SCAN SUBQUERY 31 (~1 rows)
79 |30|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 32
80 |32|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
81 |32|0|0|EXECUTE LIST SUBQUERY 33
82 |30|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 33
83 |33|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
84 |33|0|0|EXECUTE LIST SUBQUERY 34
85 |30|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 34
86 |34|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
87 |34|0|0|EXECUTE LIST SUBQUERY 35
88 |30|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 35
89 |35|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
90 |35|0|0|EXECUTE LIST SUBQUERY 36
91 |30|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 36
92 |36|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
93 |36|0|0|EXECUTE LIST SUBQUERY 37
94 |30|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 37
95 |37|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
96 |37|0|0|EXECUTE LIST SUBQUERY 38
97 |30|0|0|USE TEMP B-TREE FOR ORDER BY
98 |28|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 38
99 |39|0|0|SEARCH TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (packageId=? AND provider=? AND
instanceId=?) (~1 rows)
100|38|0|0|SCAN SUBQUERY 39 (~1 rows)
101|38|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 40
102|40|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
103|40|0|0|EXECUTE LIST SUBQUERY 41
104|38|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 41
105|41|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
106|41|0|0|EXECUTE LIST SUBQUERY 42
107|38|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 42
108|42|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
109|42|0|0|EXECUTE LIST SUBQUERY 43
110|38|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 43
111|43|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
112|43|0|0|EXECUTE LIST SUBQUERY 44
113|38|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 44
114|44|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
115|44|0|0|EXECUTE LIST SUBQUERY 45
116|38|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 45
117|45|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
118|45|0|0|EXECUTE LIST SUBQUERY 46
119|38|0|0|USE TEMP B-TREE FOR ORDER BY
120|28|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 46
121|47|0|0|SEARCH TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (packageId=? AND provider=? AND
instanceId=?) (~1 rows)
122|46|0|0|SCAN SUBQUERY 47 (~1 rows)
123|46|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 48
124|48|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
125|48|0|0|EXECUTE LIST SUBQUERY 49
126|46|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 49
127|49|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
128|49|0|0|EXECUTE LIST SUBQUERY 50
129|46|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 50
130|50|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
131|50|0|0|EXECUTE LIST SUBQUERY 51
132|46|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 51
133|51|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
134|51|0|0|EXECUTE LIST SUBQUERY 52
135|46|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 52
136|52|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
137|52|0|0|EXECUTE LIST SUBQUERY 53
138|46|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 53
139|53|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
140|53|0|0|EXECUTE LIST SUBQUERY 54
141|46|0|0|USE TEMP B-TREE FOR ORDER BY
142|28|0|0|USE TEMP B-TREE FOR ORDER BY
143|0 |0|0|USE TEMP B-TREE FOR ORDER BY
The execution plan without the final ORDER BY clause is remarkably
exactly half-1:
# | |.|.|.
--+--+-+-+--------------------------------------------------------------------------------------------------------------------------------------------------------
1 |1 |0|0|SCAN TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (~20525 rows)
2 |0 |0|0|SCAN SUBQUERY 1 AS s (~20525 rows)
3 |0 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
4 |3 |0|0|SEARCH TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (packageId=?) (~19 rows)
5 |2 |0|0|SCAN SUBQUERY 3 (~9 rows)
6 |2 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
7 |5 |0|0|SEARCH TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (packageId=? AND provider=? AND
instanceId=?) (~1 rows)
8 |4 |0|0|SCAN SUBQUERY 5 (~1 rows)
9 |4 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 6
10|6 |0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
11|6 |0|0|EXECUTE LIST SUBQUERY 7
12|4 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 7
13|7 |0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
14|7 |0|0|EXECUTE LIST SUBQUERY 8
15|4 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 8
16|8 |0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
17|8 |0|0|EXECUTE LIST SUBQUERY 9
18|4 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 9
19|9 |0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
20|9 |0|0|EXECUTE LIST SUBQUERY 10
21|4 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 10
22|10|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
23|10|0|0|EXECUTE LIST SUBQUERY 11
24|4 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 11
25|11|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
26|11|0|0|EXECUTE LIST SUBQUERY 12
27|4 |0|0|USE TEMP B-TREE FOR ORDER BY
28|2 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 12
29|13|0|0|SEARCH TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (packageId=? AND provider=? AND
instanceId=?) (~1 rows)
30|12|0|0|SCAN SUBQUERY 13 (~1 rows)
31|12|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 14
32|14|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
33|14|0|0|EXECUTE LIST SUBQUERY 15
34|12|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 15
35|15|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
36|15|0|0|EXECUTE LIST SUBQUERY 16
37|12|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 16
38|16|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
39|16|0|0|EXECUTE LIST SUBQUERY 17
40|12|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 17
41|17|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
42|17|0|0|EXECUTE LIST SUBQUERY 18
43|12|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 18
44|18|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
45|18|0|0|EXECUTE LIST SUBQUERY 19
46|12|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 19
47|19|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
48|19|0|0|EXECUTE LIST SUBQUERY 20
49|12|0|0|USE TEMP B-TREE FOR ORDER BY
50|2 |0|0|EXECUTE CORRELATED SCALAR SUBQUERY 20
51|21|0|0|SEARCH TABLE materializedInstances USING COVERING INDEX
1ab7a7b8a24dce53a34a650587c5bfdabcc785a2 (packageId=? AND provider=? AND
instanceId=?) (~1 rows)
52|20|0|0|SCAN SUBQUERY 21 (~1 rows)
53|20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 22
54|22|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
55|22|0|0|EXECUTE LIST SUBQUERY 23
56|20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 23
57|23|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
58|23|0|0|EXECUTE LIST SUBQUERY 24
59|20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 24
60|24|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
61|24|0|0|EXECUTE LIST SUBQUERY 25
62|20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 25
63|25|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
64|25|0|0|EXECUTE LIST SUBQUERY 26
65|20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 26
66|26|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
67|26|0|0|EXECUTE LIST SUBQUERY 27
68|20|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 27
69|27|0|0|SEARCH TABLE materializedPrices AS m USING COVERING INDEX
ffd0d22435eccb1020821f3ec70d038dfc7e5d91 (provider=? AND instanceId=?
AND start<?) (~1 rows)
70|27|0|0|EXECUTE LIST SUBQUERY 28
71|20|0|0|USE TEMP B-TREE FOR ORDER BY
72|2 |0|0|USE TEMP B-TREE FOR ORDER BY
