Does running an ANALYZE have any impact? On Fri, Dec 6, 2019 at 12:47 AM radovan5 <radovan.antl...@siol.net> wrote:
> Also tested with latest version 3.30.1. For showing problem I need just > 2 tables (for join). > > -- cca 30.000.000 recs > CREATE TABLE ARHDAJ( > ID_ARHDAJ integer primary key autoincrement, > ID_ARHPLA integer, > R integer, > O decimal(8,4), > Z decimal(12,2), > VEZA varchar(8) collate systemnocase); > CREATE UNIQUE INDEX ARHDAJ_IDX_ARHPLA on ARHDAJ (ID_ARHPLA,R,ID_ARHDAJ); > CREATE UNIQUE INDEX ARHDAJ_IDX_R on ARHDAJ (R,ID_ARHDAJ); > > -- cca 100 recs > CREATE TABLE IZBPLA ( > ID_PLA integer primary key autoincrement, > ID_DEL integer, > L4MM integer, > OE varchar(2) collate systemnocase, > SM varchar(4) collate systemnocase, > DN varchar(10) collate systemnocase, > DDM integer, > P integer, > U decimal(6,2), > UN decimal(6,2), > O decimal(8,4), > ZO decimal(12,2), > ZB decimal(12,2), > ZN decimal(12,2), > OS decimal(8,4), > DOH varchar(1) collate systemnocase); > CREATE UNIQUE INDEX IZBPLA_IDX_DEL on IZBPLA(ID_DEL, ID_PLA); > CREATE UNIQUE INDEX IZBPLA_IDX_P on IZBPLA(P, ID_PLA); > > Problematic sql is: > > select > R.ID_ARHDAJ, R.ID_ARHPLA, R.R, R.O, R.Z, R.VEZA > from IZBPLA P > left join ARHDAJ R on R.ID_ARHPLA = P.ID_PLA > where P.ID_PLA < 1000000000 > and R.ID_ARHDAJ is not null; > > explain query plan before 3.28.0 is: > > 0: SEARCH TABLE IZBPLA AS P USING INTEGER PRIMARY KEY (rowid<?) > 1: SEARCH TABLE ARHDAJ AS R USING INDEX ARHDAJ_IDX_ARHPLA (ID_ARHPLA=?) > > explain query plan after 3.28.0 is: > > 0: SEARCH TABLE ARHDAJ AS R USING INDEX ARHDAJ_IDX_ARHPLA (ID_ARHPLA=?) > 1: SEARCH TABLE IZBPLA AS P USING INTEGER PRIMARY KEY (rowid<?) > > Workaround is to change "and R.ID_ARHDAJ is not null" to "and > ifnull(R.ID_ARHDAJ,0) <> 0". > > So sqlite goes through large table but should go through small table and > search record in > large for every record. > > Best Regards > Radovan Antloga > > _______________________________________________ > 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
