I did some profiling with gprof to see why the query which does an extra ordering on descending docid is (surprisingly) 100 times faster than the normal query. As you can see in the results below is that the functions fts3PoslistCopy and sqlite3Fts3GetVarint get called over more than 400 million times! While they can no longer be seen in top results when you do the ordering.
I hope this information gives some insight into the problem. Normal query: Each sample counts as 0.01 seconds. % cumulative self self total time seconds seconds calls s/call s/call name 34.22 28.32 28.32 415900196 0.00 0.00 fts3PoslistCopy 32.47 55.19 26.87 95213 0.00 0.00 sqlite3Fts3DoclistPrev 24.22 75.23 20.05 433620328 0.00 0.00 sqlite3Fts3GetVarint 2.74 77.50 2.27 52590 0.00 0.00 inflate_fast 1.23 78.53 1.02 105166 0.00 0.00 adler32 0.98 79.33 0.81 129868307 0.00 0.00 CheckPtr(void*) 0.74 79.94 0.61 157728 0.00 0.00 inflate_table 0.54 80.39 0.45 10570 0.00 0.00 CheckAllPtrs() 0.31 80.65 0.26 36359346 0.00 0.00 TBitReader32::NextBits(unsigned int) 0.30 80.90 0.25 fts3PutDeltaVarint 0.29 81.14 0.24 52587 0.00 0.00 inflate 0.25 81.35 0.21 4111022 0.00 0.00 fts3EvalNextRow 0.15 81.47 0.12 8966616 0.00 0.00 fts3SegReaderNextDocid 0.08 81.54 0.07 380992 0.00 0.00 CAdminNames::GetAdmin(int, TAdminName&) 0.08 81.61 0.07 40 0.00 0.02 sqlite3Fts3SegReaderStep 0.07 81.67 0.06 4050088 0.00 0.00 sqlite3Fts3PutVarint 0.06 81.72 0.05 4675297 0.00 0.00 sqlite3Fts3MsrIncrNext 0.05 81.75 0.04 12764953 0.00 0.00 TBitReader32::NextBit() 0.05 81.80 0.04 8967171 0.00 0.00 fts3SegReaderSort 0.05 81.83 0.04 8727704 0.00 0.00 fts3EvalPhraseNext 0.05 81.88 0.04 8351619 0.00 0.00 fts3ColumnFilter 0.05 81.92 0.04 88459 0.00 0.00 sqlite3VdbeExec 0.04 81.94 0.03 8435164 0.00 0.00 sqlite3Fts3GetVarint32 0.04 81.97 0.03 623845 0.00 0.00 TBitReader32::ManyBitsOnce(int*, unsigne> 0.04 82.00 0.03 280518 0.00 0.00 MallocPrivate 0.03 82.03 0.03 5448123 0.00 0.00 std::less<int>::operator()(int const&, i> 0.02 82.05 0.02 5075842 0.00 0.00 std::_Rb_tree<int, int, std::_Identity<i> 0.02 82.07 0.02 1963669 0.00 0.00 icu::UnicodeString::doCompare(int, int, > 0.02 82.09 0.02 1927070 0.00 0.00 sqlite3GetVarint 0.02 82.11 0.02 1796038 0.00 0.00 sqlite3_free 0.02 82.13 0.02 765451 0.00 0.00 OSALTickCount 0.02 82.15 0.02 82948 0.00 0.00 sqlite3BtreeMovetoUnpacked 0.02 82.17 0.02 80590 0.00 0.00 fts3MatchinfoLcs 0.02 82.19 0.02 65892 0.00 0.00 std::_Rb_tree<icu::UnicodeString, std::p> 0.02 82.21 0.02 54190 0.00 0.00 pcache1RemoveFromHash Query ORDER BY ftsPois.docId DESC: Each sample counts as 0.01 seconds. % cumulative self self total time seconds seconds calls s/call s/call name 34.06 2.19 2.19 49596 0.00 0.00 inflate_fast 14.93 3.15 0.96 99193 0.00 0.00 adler32 11.59 3.90 0.74 98824586 0.00 0.00 CheckPtr(void*) 5.44 4.25 0.35 8270 0.00 0.00 CheckAllPtrs() 4.67 4.54 0.30 148757 0.00 0.00 inflate_table 4.12 4.81 0.27 36359381 0.00 0.00 TBitReader32::NextBits(unsigned int) 3.42 5.03 0.22 49583 0.00 0.00 inflate 1.71 5.14 0.11 77085 0.00 0.00 fts3EvalNextRow 1.24 5.22 0.08 4597135 0.00 0.00 sqlite3Fts3DoclistPrev 1.24 5.30 0.08 119 0.00 0.00 sqlite3Fts3SegReaderStep 0.93 5.36 0.06 12764989 0.00 0.00 TBitReader32::NextBit() 0.93 5.42 0.06 4597236 0.00 0.00 fts3ReversePoslist 0.93 5.48 0.06 380992 0.00 0.00 CAdminNames::GetAdmin(int, TAdminName&) 0.78 5.53 0.05 4915060 0.00 0.00 fts3SegReaderNextDocid 0.62 5.57 0.04 18906992 0.00 0.00 sqlite3Fts3GetVarint 0.62 5.61 0.04 1845836 0.00 0.00 sqlite3GetVarint 0.47 5.64 0.03 5448119 0.00 0.00 std::less<int>::operator()(int const&, int> 0.47 5.67 0.03 4597396 0.00 0.00 fts3GetReverseVarint 0.47 5.70 0.03 4299543 0.00 0.00 fts3ColumnFilter 0.47 5.73 0.03 1963865 0.00 0.00 icu::UnicodeString::doCompare(int, int, ic> 0.31 5.75 0.02 10872472 0.00 0.00 std::_Identity<int>::operator()(int const&> 0.31 5.77 0.02 628038 0.00 0.00 CGameFileCache::ReadInternalBuffer(unsigne> 0.31 5.79 0.02 623855 0.00 0.00 TBitReader32::ManyBitsOnce(int*, unsigned > 0.31 5.81 0.02 563752 0.00 0.00 CMutexInstance::Instance() 0.31 5.83 0.02 247354 0.00 0.00 std::_Rb_tree<int, int, std::_Identity<int> 0.31 5.85 0.02 84934 0.00 0.00 sqlite3VdbeExec 0.31 5.87 0.02 79426 0.00 0.00 sqlite3BtreeMovetoUnpacked 0.31 5.89 0.02 1124 0.00 0.00 DoCollapseHuffmanTree(THuffmanTree const*,> 0.31 5.91 0.02 142 0.00 0.00 CAdminNames::DoInit() 0.23 5.92 0.01 2126673 0.00 0.00 Tdes::Ptr() const 0.16 5.93 0.01 11556964 0.00 0.00 icu::UnicodeString::length() const 0.16 5.95 0.01 5075842 0.00 0.00 std::_Rb_tree<int, int, std::_Identity<int> 0.16 5.96 0.01 4916144 0.00 0.00 fts3SegReaderSort 0.16 5.96 0.01 4914873 0.00 0.00 fts3SegReaderRequire 0.16 5.97 0.01 4781064 0.00 0.00 fts3PoslistCopy _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
