Query plan changed, It's a bug ________________________________ 发件人: Alessandro Fardin<mailto:alessandro.far...@gavazziacbu.it> 发送时间: 2016/7/26 16:37 收件人: sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org> 主题: [sqlite] sqlite 3.13.0 does not use indexes as 3.8.11.1 does
After updating from sqlite 3.8.11.1 to sqlite 3.13.0. The Query planner with the same SELECT statement on same table with the same indexes does not use index at all, but parse the entire table. Of course this causes a dramatically slow down of the application. As temporary work around we have have added to the query the INDEXED BY energy_d_dateTimeIdx statement. In sqlite 3.8.11.1 the select was issued by using the energy_d_dateTimeIdx index Follows the shema of the table and indexes. CREATE TABLE IF NOT EXISTS 'hst_energy_d' ( 'timestamp' INTEGER, 'pupdate' INTEGER, 'idinstrum' INTEGER NOT NULL, 'enflag' INTEGER NOT NULL DEFAULT 0, 'recdate' TEXT(10) NOT NULL, 'rectime' TEXT(8) NOT NULL, 'Vlnsys' REAL, 'Vl1n' REAL, 'Vl2n' REAL, 'Vl3n' REAL, 'Vllsys' REAL, 'Vl1l2' REAL, 'Vl2l3' REAL, 'Vl3l1' REAL, 'Al1' REAL, 'Al2' REAL, 'Al3' REAL, 'kWsys' REAL, 'kWl1' REAL, 'kWl2' REAL, 'kWl3' REAL, 'kWhac' REAL, 'kWhacn' REAL, 'kvarsys' REAL, 'kvarl1' REAL, 'kvarl2' REAL, 'kvarl3' REAL, 'kvarhn' REAL, 'kvarh' REAL, 'kvarhacC' REAL, 'kvarhacL' REAL, 'kVAsys' REAL, 'kVAl1' REAL, 'kVAl2' REAL, 'kVAl3' REAL, 'PSeq' REAL, 'THDAl1' REAL, 'THDAl2' REAL, 'THDAl3' REAL, 'THDVl1n' REAL, 'THDVl2n' REAL, 'THDVl3n' REAL, 'kWhl1' REAL, 'kWhl2' REAL, 'kWhl3' REAL, 'counter1' REAL, 'counter2' REAL, 'counter3' REAL, 'Hz' REAL, 'An' REAL, 'Hour' REAL, 'Hourn' REAL, 'Alsys' REAL, 'kvarhl1' REAL, 'kvarhl2' REAL, 'kvarhl3' REAL, 'kvarhnl1' REAL, 'kvarhnl2' REAL, 'kvarhnl3' REAL, 'kWhnl1' REAL, 'kWhnl2' REAL, 'kWhnl3' REAL, 'kVAh' REAL, 'kVAhl1' REAL, 'kVAhl2' REAL, 'kVAhl3' REAL, 'PFsys' REAL, 'PFl1' REAL, 'PFl2' REAL, 'PFl3' REAL, 'Wdmd' REAL, 'vardmd' REAL, 'VAdmd' REAL ); CREATE INDEX IF NOT EXISTS energy_d_dateTimeIdx ON hst_energy_d (recdate ASC,rectime ASC,idinstrum ASC,enflag ASC); CREATE INDEX IF NOT EXISTS hst_energy_d_index_timestamp ON hst_energy_d (timestamp ASC); CREATE INDEX IF NOT EXISTS hst_energy_d_index_pupdate ON hst_energy_d (pupdate ASC); And now the SELECT STATEMENT SELECT pupdate,idinstrum,Vlnsys,Vl1n,Vl2n,Vl3n,Vllsys,Vl1l2,Vl2l3,Vl3l1, Al1,Al2,Al3,kWsys,kWl1,kWl2,kWl3,kWhac, kWhacn,kvarsys,kvarl1,kvarl2,kvarl3,kvarhn,kvarh, kvarhacC,kvarhacL,kVAsys,kVAl1,kVAl2 ,kVAl3,PSeq, THDAl1,THDAl2,THDAl3,THDVl1n,THDVl2n,THDVl3n, kWhl1 ,kWhl2 ,kWhl3 ,counter1,counter2,counter3,Hz,An,Hour,Hourn, Alsys,kvarhl1,kvarhl2,kvarhl3,kvarhnl1,kvarhnl2,kvarhnl3,kWhnl1,kWhnl2, kWhnl3,kVAh,kVAhl1,kVAhl2,kVAhl3, PFsys,PFl1,PFl2,PFl3,Wdmd,vardmd,VAdmd FROM hst_energy_d WHERE enflag = 0 ORDER BY recdate DESC, rectime DESC LIMIT 1; --------------------------------------------------- Alessandro Fardin Carlo Gavazzi Controls SpA - R&D Via Cima i Prà, 9/H 32014 Ponte Nelle Alpi (BL) - ITALY Phone: (+39)0437.355811 / Fax: (+39)0437.355880 Visit our site: www.gavazziautomation.com e-mail: alessandro.far...@gavazziacbu.it --------------------------------------------------- _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
