The sample column has some sensitive data that I can't share, so I'll have to change it and then try to reproduce the problem (I removed it before because I assumed that it wouldn't be important).
On Mon, Jun 23, 2014 at 3:26 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 06/23/2014 05:48 AM, João Ramos wrote: > >> Here you go: >> >> sqlite_stat1 (before - good planning) >> HistoryEntry idx_HistoryEntry_uri_historyOrder 14992 44 5 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 14992 6 2 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14992 2999 2 >> >> sqlite_stat1 (after - bad planning) >> HistoryEntry idx_HistoryEntry_uri_historyOrder 15492 45 5 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 15492 5 2 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 15492 3099 2 >> > > Unfortunately the last column of the sqlite_stat4 data is missing, likely > because it contains embedded 0x00 bytes. And without the sqlite_stat4 data, > it seems that SQLite picks the "good" query plan in either case. > > Can you dump the sqlite_stat1 and sqlite_stat4 tables using the shell > ".dump" command instead? > > Thanks, > Dan. > > > > > >> sqlite_stat4 (before - good planning) >> HistoryEntry idx_HistoryEntry_uri_historyOrder 366 340 1 78 78 81 17 17 >> 81 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 722 489 1 566 566 661 38 >> 96 >> 661 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 25 25 1 1650 1650 1665 56 >> 352 1665 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 862 747 1 2569 2569 2899 >> 106 >> 447 2899 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 862 1 1 2569 3331 3331 106 >> 462 3331 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 2026 1789 1 3825 3825 >> 4997 131 >> 660 4997 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 143 113 1 6178 6178 6232 >> 154 >> 931 6232 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 181 180 1 6412 6412 6477 >> 162 >> 974 6477 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 2 2 1 6663 6663 6663 169 >> 984 >> 6663 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 1251 988 1 6953 6953 7488 >> 186 >> 1062 7488 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 379 370 1 8260 8260 8329 >> 195 >> 1361 8329 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 135 92 1 8915 8915 8965 >> 218 >> 1439 8965 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 196 194 1 9128 9128 9129 >> 227 >> 1501 9129 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 280 170 1 9622 9622 9650 >> 237 >> 1579 9650 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 1228 1045 1 9954 9954 >> 9995 244 >> 1709 9995 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 178 170 1 11192 11192 >> 11322 246 >> 1898 11322 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 312 299 1 11503 11503 >> 11661 252 >> 1963 11661 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 546 543 1 11921 11921 >> 12037 258 >> 1994 12037 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 169 113 1 12574 12574 >> 12586 261 >> 2013 12586 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 216 115 1 12862 12862 >> 12973 265 >> 2187 12973 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 388 379 1 13101 13101 >> 13327 270 >> 2291 13327 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 366 351 1 13722 13722 >> 13973 295 >> 2388 13973 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 200 1 1 14482 14530 14530 >> 331 >> 2749 14530 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 233 2 1 14684 14848 14849 >> 333 >> 3067 14849 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0 >> 1665 1665 0 1663 1665 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0 >> 3331 3331 0 3329 3331 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0 >> 4997 4997 0 4995 4997 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0 >> 6663 6663 0 6661 6663 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0 >> 8329 8329 0 8327 8329 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0 >> 9995 9995 0 9993 9995 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0 >> 11661 11661 0 11659 11661 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 5 1 1 12392 >> 12392 12392 377 12390 12392 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 12557 >> 12558 12558 530 12556 12558 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 5 1 1 13042 >> 13044 13044 998 13042 13044 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13049 >> 13049 13049 1000 13047 13049 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13061 >> 13061 13061 1006 13059 13061 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13210 >> 13212 13212 1150 13210 13212 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 1 1 1 13327 >> 13327 13327 1261 13325 13327 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13411 >> 13411 13411 1344 13409 13411 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 6 1 1 13572 >> 13573 13573 1498 13571 13573 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 13582 >> 13585 13585 1503 13583 13585 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 13726 >> 13727 13727 1643 13725 13727 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 8 1 1 13978 >> 13978 13978 1890 13976 13978 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 14203 >> 14206 14206 2098 14204 14206 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 14317 >> 14318 14318 2208 14316 14318 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 14452 >> 14454 14454 2332 14452 14454 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 14687 >> 14688 14688 2559 14686 14688 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 14836 >> 14838 14838 2697 14836 14838 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 661 661 0 >> 661 >> 661 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 1665 1665 0 >> 1665 1665 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 2537 2537 0 >> 2537 2537 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 3331 3331 0 >> 3331 3331 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 3332 3332 0 >> 3332 3332 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 4997 4997 0 >> 4997 4997 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 5061 5061 0 >> 5061 5061 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 6663 6663 0 >> 6663 6663 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 7488 7488 0 >> 7488 7488 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 8329 8329 0 >> 8329 8329 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 9129 9129 0 >> 9129 9129 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 9650 9650 0 >> 9650 9650 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 9995 9995 0 >> 9995 9995 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 10120 >> 10120 0 >> 10120 10120 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 10500 >> 10500 0 >> 10500 10500 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 11661 >> 11661 0 >> 11661 11661 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 12037 >> 12037 0 >> 12037 12037 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 13068 >> 13068 0 >> 13068 13068 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 13327 >> 13327 0 >> 13327 13327 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14476 1 1 0 14301 >> 14301 0 >> 14301 14301 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 424 1 1 14476 14530 >> 14530 1 >> 14530 14530 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 11 1 1 14900 14910 >> 14910 2 >> 14910 14910 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 58 58 1 14911 14911 >> 14919 3 >> 14911 14919 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 23 1 1 14969 14979 >> 14979 4 >> 14922 14979 >> >> sqlite_stat1 (after - bad planning) >> HistoryEntry idx_HistoryEntry_uri_historyOrder 366 340 1 78 78 230 17 17 >> 230 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 791 489 1 566 566 933 38 >> 96 >> 933 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 25 25 1 1719 1719 1721 56 >> 421 1721 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 872 747 1 2645 2645 2995 >> 107 >> 521 2995 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 872 1 1 2645 3443 3443 107 >> 557 3443 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 2136 1789 1 3930 3930 >> 5165 132 >> 756 5165 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 181 180 1 6627 6627 6631 >> 163 >> 1179 6631 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 28 28 1 6880 6880 6887 171 >> 1190 6887 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 1267 988 1 7175 7175 8036 >> 188 >> 1274 8036 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 379 370 1 8498 8498 8609 >> 197 >> 1589 8609 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 196 194 1 9366 9366 9509 >> 229 >> 1729 9509 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 288 170 1 9880 9880 9998 >> 239 >> 1827 9998 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 1240 1045 1 10220 10220 >> 10331 246 >> 1965 10331 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 178 170 1 11470 11470 >> 11507 248 >> 2166 11507 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 313 299 1 11781 11781 >> 12053 254 >> 2231 12053 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 546 543 1 12200 12200 >> 12319 260 >> 2263 12319 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 169 113 1 12853 12853 >> 12965 263 >> 2282 12965 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 216 115 1 13141 13141 >> 13255 267 >> 2456 13255 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 388 379 1 13380 13380 >> 13632 272 >> 2560 13632 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 6 2 1 13772 13774 13775 >> 274 >> 2573 13775 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 368 351 1 14001 14001 >> 14157 297 >> 2657 14157 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 304 1 1 14384 14554 14554 >> 299 >> 2846 14554 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 200 1 1 14972 15101 15101 >> 333 >> 3310 15101 >> HistoryEntry idx_HistoryEntry_uri_historyOrder 233 2 1 15174 15338 15339 >> 335 >> 3547 15339 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0 >> 1721 1721 0 1719 1721 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0 >> 3443 3443 0 3441 3443 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0 >> 5165 5165 0 5163 5165 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0 >> 6887 6887 0 6885 6887 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0 >> 8609 8609 0 8607 8609 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0 >> 10331 10331 0 10329 10331 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 1 1 1 12053 >> 12053 12053 44 12051 12053 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 5 1 1 12392 >> 12393 12393 377 12391 12393 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 12469 >> 12471 12471 448 12469 12471 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 5 1 1 13042 >> 13045 13045 998 13043 13045 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13210 >> 13210 13210 1150 13208 13210 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13411 >> 13412 13412 1344 13410 13412 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 6 1 1 13572 >> 13575 13575 1498 13573 13575 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 13582 >> 13583 13583 1503 13581 13583 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 13726 >> 13729 13729 1643 13727 13729 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 1 1 1 13775 >> 13775 13775 1688 13773 13775 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 8 1 1 13978 >> 13980 13980 1890 13978 13980 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 14203 >> 14204 14204 2098 14202 14204 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 14317 >> 14320 14320 2208 14318 14320 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 14452 >> 14452 14452 2332 14450 14452 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 15057 >> 15057 15057 2909 15055 15057 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 15189 >> 15189 15189 3039 15187 15189 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 15266 >> 15266 15266 3114 15264 15266 >> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 15272 >> 15273 15273 3117 15271 15273 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 230 230 0 >> 230 >> 230 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 1580 1580 0 >> 1580 1580 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 1721 1721 0 >> 1721 1721 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 3443 3443 0 >> 3443 3443 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 3524 3524 0 >> 3524 3524 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 5028 5028 0 >> 5028 5028 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 5032 5032 0 >> 5032 5032 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 5165 5165 0 >> 5165 5165 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 5272 5272 0 >> 5272 5272 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 5305 5305 0 >> 5305 5305 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 6887 6887 0 >> 6887 6887 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 8036 8036 0 >> 8036 8036 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 8609 8609 0 >> 8609 8609 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 8653 8653 0 >> 8653 8653 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 9306 9306 0 >> 9306 9306 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 10331 >> 10331 0 >> 10331 10331 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 11098 >> 11098 0 >> 11098 11098 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 11261 >> 11261 0 >> 11261 11261 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 12053 >> 12053 0 >> 12053 12053 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 13775 >> 13775 0 >> 13775 13775 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 468 1 1 14924 15266 >> 15266 1 >> 15266 15266 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 12 1 1 15392 15395 >> 15395 2 >> 15395 15395 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 64 64 1 15404 15404 >> 15409 3 >> 15404 15409 >> HistoryEntry idx_HistoryEntry_sourceType_sourceId 24 1 1 15468 15478 >> 15478 4 >> 15415 15478 >> >> >> >> On Sun, Jun 22, 2014 at 9:46 PM, Richard Hipp <d...@sqlite.org> wrote: >> >> On Sun, Jun 22, 2014 at 2:05 PM, João Ramos <joao.si...@gmail.com> >>> wrote: >>> >>> Everything looks fine and the query runs smoothly, but at around 15000 >>>> entries, the query plan changes... >>>> >>>> >>> Please send the content of the sqlite_stat1 table. Also sqlite_stat3 or >>> sqlite_stat4, if they exist. Do this both for before and after the >>> slowdown. Thanks. >>> >>> -- >>> D. Richard Hipp >>> d...@sqlite.org >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
