On Thu, Nov 21, 2013 at 1:30 PM, Andreas Kloeckner <inf...@tiker.net> wrote:
> Hi there,
>
> I'm encountering a fairly severe performance regression ("too quick to
> notice" -> "multiple seconds") with SQLite 3.8.1 as compared to
> 3.7.13. Both versions are installed from packages shipped by Debian.
>
> I've attached the command line and output for an "explain" of both
> queries below. I'd be happy to supply the (fairly small--3-ish MB)
> database privately if needed.
>
> (If anyone is wondering, the query was generated by SQLAlchemy.)
>
> I'd much appreciate any help with this. Let me know if there's something
> else I can test or provide.
>
Need either (1) your schema or (2) the sample database.
>
> Thanks!
> Andreas
>
>
> SQLite version 3.8.1 2013-10-17 12:57:35
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> explain SELECT currentitemversions.id AS currentitemversions_id,
> currentitemversions.item_id AS currentitemversions_item_id,
> currentitemversions.timestamp AS currentitemversi
> ons_timestamp, currentitemversions.contents AS
> currentitemversions_contents, currentitemversions.all_day AS
> currentitemversions_all_day, currentitemversions.start_date AS currentitem
> versions_start_date, currentitemversions.end_date AS
> currentitemversions_end_date, currentitemversions.bump_interval AS
> currentitemversions_bump_interval, currentitemversions.hide_un
> til AS currentitemversions_hide_until, currentitemversions.highlight_at AS
> currentitemversions_highlight_at, tags.id AS tags_id, tags.name AS
> tags_name FROM (SELECT itemversions.id A
> S id, itemversions.item_id AS item_id, itemversions.timestamp AS
> timestamp, itemversions.contents AS contents, itemversions.all_day AS
> all_day, itemversions.start_date AS start_date,
> itemversions.end_date AS end_date, itemversions.bump_interval AS
> bump_interval, itemversions.hide_until AS hide_until,
> itemversions.highlight_at AS highlight_at FROM itemversions JO
> IN (SELECT itemversions.item_id AS item_id, max(itemversions.timestamp) AS
> max_timestamp FROM itemversions GROUP BY itemversions.item_id) AS max_ts ON
> max_ts.item_id = itemversions.i
> tem_id AND itemversions.timestamp = max_ts.max_timestamp LEFT OUTER JOIN
> (SELECT viewordering_entries.id AS id,
> viewordering_entries.viewordering_id AS viewordering_id, viewordering_
> entries.item_id AS item_id, viewordering_entries.weight AS weight FROM
> viewordering_entries WHERE viewordering_entries.viewordering_id = 2367) AS
> vo_entries ON itemversions.item_id =
> vo_entries.item_id WHERE itemversions.contents IS NOT NULL AND (EXISTS
> (SELECT 1 FROM itemversions_tags, tags WHERE itemversions.id =
> itemversions_tags.itemversion_id AND tags.id =
> itemversions_tags.tag_id AND tags.id = 269)) AND (itemversions.hide_until
> < 1385057665.481467 OR itemversions.hide_until IS NULL) GROUP BY
> itemversions.item_id ORDER BY vo_entries.we
> ight) AS currentitemversions LEFT OUTER JOIN itemversions_tags ON
> currentitemversions.id = itemversions_tags.itemversion_id LEFT OUTER JOIN
> tags ON itemversions_tags.tag_id = tags.id
> ;
> 0|Trace|0|0|0||00|
> 1|Integer|232|1|0||00|
> 2|Once|0|232|0||00|
> 3|Integer|55|2|0||00|
> 4|Once|1|55|0||00|
> 5|OpenEphemeral|2|2|0||00|
> 6|SorterOpen|10|3|0|keyinfo(1,BINARY)|00|
> 7|Integer|0|7|0||00|
> 8|Integer|0|6|0||00|
> 9|Null|0|10|10||00|
> 10|Gosub|9|51|0||00|
> 11|Goto|0|281|0||00|
> 12|OpenRead|3|4|0|3|00|
> 13|Rewind|3|20|0||00|
> 14|Column|3|1|12||00|
> 15|Sequence|10|13|0||00|
> 16|Column|3|2|14||00|
> 17|MakeRecord|12|3|15||00|
> 18|SorterInsert|10|15|0||00|
> 19|Next|3|14|0||01|
> 20|Close|3|0|0||00|
> 21|OpenPseudo|11|15|3||00|
> 22|SorterSort|10|55|0||00|
> 23|SorterData|10|15|0||00|
> 24|Column|11|0|11||20|
> 25|Compare|10|11|1|keyinfo(1,BINARY)|00|
> 26|Jump|27|31|27||00|
> 27|Move|11|10|0||00|
> 28|Gosub|8|42|0||00|
> 29|IfPos|7|55|0||00|
> 30|Gosub|9|51|0||00|
> 31|Column|11|2|12||00|
> 32|CollSeq|16|0|0|collseq(BINARY)|00|
> 33|AggStep|0|12|4|max(1)|01|
> 34|If|16|36|0||00|
> 35|Column|11|0|3||00|
> 36|Integer|1|6|0||00|
> 37|SorterNext|10|23|0||00|
> 38|Gosub|8|42|0||00|
> 39|Goto|0|55|0||00|
> 40|Integer|1|7|0||00|
> 41|Return|8|0|0||00|
> 42|IfPos|6|44|0||00|
> 43|Return|8|0|0||00|
> 44|AggFinal|4|1|0|max(1)|00|
> 45|SCopy|3|17|0||00|
> 46|SCopy|4|18|0||00|
> 47|MakeRecord|17|2|19||00|
> 48|NewRowid|2|20|0||00|
> 49|Insert|2|19|20||08|
> 50|Return|8|0|0||00|
> 51|Null|0|3|0||00|
> 52|Null|0|5|0||00|
> 53|Null|0|4|0||00|
> 54|Return|9|0|0||00|
> 55|Return|2|0|0||00|
> 56|Integer|73|21|0||00|
> 57|Once|2|73|0||00|
> 58|OpenEphemeral|4|4|0||00|
> 59|OpenRead|5|6|0|4|00|
> 60|Rewind|5|72|0||00|
> 61|Column|5|1|22||00|
> 62|Integer|2367|23|0||00|
> 63|Ne|23|71|22|collseq(BINARY)|6c|
> 64|Rowid|5|24|0||00|
> 65|Column|5|1|25||00|
> 66|Column|5|2|26||00|
> 67|Column|5|3|27||00|
> 68|MakeRecord|24|4|22||00|
> 69|NewRowid|4|23|0||00|
> 70|Insert|4|22|23||08|
> 71|Next|5|61|0||01|
> 72|Close|5|0|0||00|
> 73|Return|21|0|0||00|
> 74|SorterOpen|12|3|0|keyinfo(1,BINARY)|00|
> 75|OpenEphemeral|0|10|0||00|
> 76|SorterOpen|13|12|0|keyinfo(1,BINARY)|00|
> 77|Integer|0|40|0||00|
> 78|Integer|0|39|0||00|
> 79|Null|0|43|43||00|
> 80|Gosub|42|213|0||00|
> 81|OpenRead|1|4|0|11|00|
> 82|OpenRead|14|12|0|keyinfo(1,BINARY)|00|
> 83|Rewind|2|165|0||00|
> 84|Column|2|1|45||00|
> 85|IsNull|45|164|0||00|
> 86|Affinity|45|1|0|c|00|
> 87|SeekGe|14|164|45|1|00|
> 88|IdxGE|14|164|45|1|01|
> 89|IdxRowid|14|46|0||00|
> 90|Seek|1|46|0||00|
> 91|Column|1|4|47||00|
> 92|IsNull|47|163|0||00|
> 93|Integer|0|49|0||00|
> 94|Integer|1|50|0||00|
> 95|OpenRead|9|2|0|0|00|
> 96|OpenRead|8|5|0|2|00|
> 97|OpenRead|15|13|0|keyinfo(1,BINARY)|00|
> 98|Integer|269|47|0||00|
> 99|MustBeInt|47|114|0||00|
> 100|NotExists|9|114|47||00|
> 101|Integer|269|51|0||00|
> 102|SeekGe|15|114|51|1|00|
> 103|IdxGE|15|114|51|1|01|
> 104|IdxRowid|15|52|0||00|
> 105|Seek|8|52|0||00|
> 106|IdxRowid|14|53|0||00|
> 107|Column|8|0|54||00|
> 108|Ne|54|113|53|collseq(BINARY)|6b|
> 109|Column|15|0|55||00|
> 110|Ne|55|113|47|collseq(BINARY)|6b|
> 111|Integer|1|49|0||00|
> 112|IfZero|50|114|-1||00|
> 113|Next|15|103|0||00|
> 114|Close|9|0|0||00|
> 115|Close|8|0|0||00|
> 116|Close|15|0|0||00|
> 117|IfNot|49|163|1||00|
> 118|Column|1|8|48||00|
> 119|RealAffinity|48|0|0||00|
> 120|Real|0|55|0|1385057665.481467|00|
> 121|Lt|55|123|48|collseq(BINARY)|65|
> 122|NotNull|48|163|0||00|
> 123|Column|2|0|55||00|
> 124|Column|1|1|54||00|
> 125|Ne|54|163|55|collseq(BINARY)|6b|
> 126|Once|3|135|0||00|
> 127|OpenAutoindex|16|3|0|keyinfo(2,BINARY,BINARY)|00|
> 128|Rewind|4|135|0||00|
> 129|Rowid|4|59|0||00|
> 130|Column|4|2|57||00|
> 131|Column|4|3|58||00|
> 132|MakeRecord|57|3|46|ddd|00|
> 133|IdxInsert|16|46|0||10|
> 134|Next|4|129|0||03|
> 135|Integer|0|60|0||00|
> 136|IsNull|54|160|0||00|
> 137|Affinity|54|1|0|d|00|
> 138|SeekGe|16|160|54|1|00|
> 139|IdxGE|16|160|54|1|01|
> 140|Integer|1|60|0||00|
> 141|Column|1|1|62||00|
> 142|Sequence|13|63|0||00|
> 143|IdxRowid|14|64|0||00|
> 144|Column|14|0|65||00|
> 145|Column|1|4|66||00|
> 146|Column|1|10|67||00|
> 147|Column|1|5|68||00|
> 148|RealAffinity|68|0|0||00|
> 149|Column|1|6|69||00|
> 150|RealAffinity|69|0|0||00|
> 151|Column|1|7|70||00|
> 152|Column|1|8|71||00|
> 153|RealAffinity|71|0|0||00|
> 154|Column|1|9|72||00|
> 155|RealAffinity|72|0|0||00|
> 156|Column|16|1|73||00|
> 157|MakeRecord|62|12|55||00|
> 158|SorterInsert|13|55|0||00|
> 159|Next|16|139|0||00|
> 160|IfPos|60|163|0||00|
> 161|NullRow|16|0|0||00|
> 162|Goto|0|140|0||00|
> 163|Next|14|88|0||00|
> 164|Next|2|84|0||01|
> 165|Close|1|0|0||00|
> 166|Close|14|0|0||00|
> 167|OpenPseudo|17|55|12||00|
> 168|SorterSort|13|225|0||00|
> 169|SorterData|13|55|0||00|
> 170|Column|17|0|44||20|
> 171|Compare|43|44|1|keyinfo(1,BINARY)|00|
> 172|Jump|173|177|173||00|
> 173|Move|44|43|0||00|
> 174|Gosub|41|194|0||00|
> 175|IfPos|40|225|0||00|
> 176|Gosub|42|213|0||00|
> 177|Column|17|2|28||00|
> 178|Column|17|0|29||00|
> 179|Column|17|3|30||00|
> 180|Column|17|4|31||00|
> 181|Column|17|5|32||00|
> 182|Column|17|6|33||00|
> 183|Column|17|7|34||00|
> 184|Column|17|8|35||00|
> 185|Column|17|9|36||00|
> 186|Column|17|10|37||00|
> 187|Column|17|11|38||00|
> 188|Integer|1|39|0||00|
> 189|SorterNext|13|169|0||00|
> 190|Gosub|41|194|0||00|
> 191|Goto|0|225|0||00|
> 192|Integer|1|40|0||00|
> 193|Return|41|0|0||00|
> 194|IfPos|39|196|0||00|
> 195|Return|41|0|0||00|
> 196|SCopy|28|74|0||00|
> 197|SCopy|29|75|0||00|
> 198|SCopy|30|76|0||00|
> 199|SCopy|31|77|0||00|
> 200|SCopy|32|78|0||00|
> 201|SCopy|33|79|0||00|
> 202|SCopy|34|80|0||00|
> 203|SCopy|35|81|0||00|
> 204|SCopy|36|82|0||00|
> 205|SCopy|37|83|0||00|
> 206|MakeRecord|74|10|61||00|
> 207|SCopy|38|62|0||00|
> 208|Sequence|12|63|0||00|
> 209|Move|61|64|0||00|
> 210|MakeRecord|62|3|46||00|
> 211|SorterInsert|12|46|0||00|
> 212|Return|41|0|0||00|
> 213|Null|0|28|0||00|
> 214|Null|0|29|0||00|
> 215|Null|0|30|0||00|
> 216|Null|0|31|0||00|
> 217|Null|0|32|0||00|
> 218|Null|0|33|0||00|
> 219|Null|0|34|0||00|
> 220|Null|0|35|0||00|
> 221|Null|0|36|0||00|
> 222|Null|0|37|0||00|
> 223|Null|0|38|0||00|
> 224|Return|42|0|0||00|
> 225|OpenPseudo|18|84|3||00|
> 226|SorterSort|12|232|0||00|
> 227|SorterData|12|84|0||00|
> 228|Column|18|2|61||20|
> 229|NewRowid|0|46|0||00|
> 230|Insert|0|61|46||08|
> 231|SorterNext|12|227|0||00|
> 232|Return|1|0|0||00|
> 233|OpenRead|6|5|0|2|00|
> 234|OpenRead|19|14|0|keyinfo(1,BINARY)|00|
> 235|OpenRead|7|2|0|2|00|
> 236|Rewind|0|277|0||00|
> 237|Integer|0|85|0||00|
> 238|Column|0|0|86||00|
> 239|IsNull|86|272|0||00|
> 240|Affinity|86|1|0|d|00|
> 241|SeekGe|19|272|86|1|00|
> 242|IdxGE|19|272|86|1|01|
> 243|IdxRowid|19|87|0||00|
> 244|Seek|6|87|0||00|
> 245|Integer|1|85|0||00|
> 246|Integer|0|88|0||00|
> 247|Column|6|1|87||00|
> 248|MustBeInt|87|268|0||00|
> 249|NotExists|7|268|87||00|
> 250|Integer|1|88|0||00|
> 251|Column|0|0|89||00|
> 252|Column|0|1|90||00|
> 253|Column|0|2|91||00|
> 254|Column|0|3|92||00|
> 255|Column|0|4|93||00|
> 256|Column|0|5|94||00|
> 257|RealAffinity|94|0|0||00|
> 258|Column|0|6|95||00|
> 259|RealAffinity|95|0|0||00|
> 260|Column|0|7|96||00|
> 261|Column|0|8|97||00|
> 262|RealAffinity|97|0|0||00|
> 263|Column|0|9|98||00|
> 264|RealAffinity|98|0|0||00|
> 265|Rowid|7|99|0||00|
> 266|Column|7|1|100||00|
> 267|ResultRow|89|12|0||00|
> 268|IfPos|88|271|0||00|
> 269|NullRow|7|0|0||00|
> 270|Goto|0|250|0||00|
> 271|Next|19|242|0||00|
> 272|IfPos|85|276|0||00|
> 273|NullRow|6|0|0||00|
> 274|NullRow|19|0|0||00|
> 275|Goto|0|245|0||00|
> 276|Next|0|237|0||01|
> 277|Close|6|0|0||00|
> 278|Close|19|0|0||00|
> 279|Close|7|0|0||00|
> 280|Halt|0|0|0||00|
> 281|Transaction|0|0|0||00|
> 282|VerifyCookie|0|26|0||00|
> 283|TableLock|0|4|0|itemversions|00|
> 284|TableLock|0|6|0|viewordering_entries|00|
> 285|TableLock|0|2|0|tags|00|
> 286|TableLock|0|5|0|itemversions_tags|00|
> 287|Goto|0|12|0||00|
> sqlite>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
