SQLite scans the largest table in the query where a very small table is available

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite scans the largest table in the query where a very small table is available

Yuri
I have the select query over the linked chain of tables with all needed
indices present.
All but one tables (m,h,e,w) have row count ~300,000-700,000, mt table
has only ~150 rows.

Obviously, based on the row count, scan should begin from the table mt.
However, SQLite actually scans beginning from m, and the query returning
only ~150 rows runs for 2+ seconds.

Does SQLite optimizer look at the row counts? (I think it does because
the plans are different when there are no rows present).
How to correct the problem?

---schema.sql---
CREATE TABLE w
  (w_id      INTEGER PRIMARY KEY AUTOINCREMENT,
   t         TEXT NOT NULL UNIQUE);
CREATE TABLE e
  (e_id      INTEGER PRIMARY KEY AUTOINCREMENT,
   w_id      INTEGER NOT NULL);
CREATE TABLE h
  (h_id      INTEGER PRIMARY KEY AUTOINCREMENT,
   e_id      INTEGER NOT NULL,
   FOREIGN KEY(e_id) REFERENCES e(e_id));
CREATE TABLE m
  (m_id     INTEGER PRIMARY KEY AUTOINCREMENT,
   h_id      INTEGER NOT NULL,
   FOREIGN KEY(h_id) REFERENCES h(h_id));
CREATE TABLE mt
  (m_id      INTEGER PRIMARY KEY,
   FOREIGN KEY(m_id) REFERENCES m(m_id));

---query.sql---
select
   h.h_id,
   w.t
from
   w w,
   e e,
   h h,
   m m,
   mt mt
where
   e.w_id = w.w_id
   and
   h.e_id = e.e_id
   and
   m.h_id = h.h_id
   and
   mt.m_id = m.m_id
group by
   e.w_id;

Plan goes like this:
0|0|3|SCAN TABLE m AS m USING COVERING INDEX m_h_index
0|1|2|SEARCH TABLE h AS h USING INTEGER PRIMARY KEY (rowid=?)
....

Yuri

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite scans the largest table in the query where a very small table is available

Matthias-Christian Ott
On 2016-01-20 18:14, Yuri wrote:
> Does SQLite optimizer look at the row counts? (I think it does because
> the plans are different when there are no rows present).
> How to correct the problem?

If you want to do a manual optimization, the following describes how to
set the order of joins manually:

https://sqlite.org/optoverview.html#table_order

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite scans the largest table in the query where a very small table is available

Hick Gunter
In reply to this post by Yuri
Have you tried running ANALYZE on a representative dataset? This will determine the "shape" of your tables and allow the query planner to make better guesses of the costs associated with each join.

If, after ANALYZE, you still feel the need to improve over the query planners' ordering, you can still use CROSS JOIN to force a certain order of tables.

-----Urspr√ľngliche Nachricht-----
Von: [hidden email] [mailto:[hidden email]] Im Auftrag von Yuri
Gesendet: Mittwoch, 20. Jänner 2016 18:14
An: General Discussion of SQLite Database
Betreff: [sqlite] SQLite scans the largest table in the query where a very small table is available

I have the select query over the linked chain of tables with all needed indices present.
All but one tables (m,h,e,w) have row count ~300,000-700,000, mt table has only ~150 rows.

Obviously, based on the row count, scan should begin from the table mt.
However, SQLite actually scans beginning from m, and the query returning only ~150 rows runs for 2+ seconds.

Does SQLite optimizer look at the row counts? (I think it does because the plans are different when there are no rows present).
How to correct the problem?

---schema.sql---
CREATE TABLE w
  (w_id      INTEGER PRIMARY KEY AUTOINCREMENT,
   t         TEXT NOT NULL UNIQUE);
CREATE TABLE e
  (e_id      INTEGER PRIMARY KEY AUTOINCREMENT,
   w_id      INTEGER NOT NULL);
CREATE TABLE h
  (h_id      INTEGER PRIMARY KEY AUTOINCREMENT,
   e_id      INTEGER NOT NULL,
   FOREIGN KEY(e_id) REFERENCES e(e_id)); CREATE TABLE m
  (m_id     INTEGER PRIMARY KEY AUTOINCREMENT,
   h_id      INTEGER NOT NULL,
   FOREIGN KEY(h_id) REFERENCES h(h_id)); CREATE TABLE mt
  (m_id      INTEGER PRIMARY KEY,
   FOREIGN KEY(m_id) REFERENCES m(m_id));

---query.sql---
select
   h.h_id,
   w.t
from
   w w,
   e e,
   h h,
   m m,
   mt mt
where
   e.w_id = w.w_id
   and
   h.e_id = e.e_id
   and
   m.h_id = h.h_id
   and
   mt.m_id = m.m_id
group by
   e.w_id;

Plan goes like this:
0|0|3|SCAN TABLE m AS m USING COVERING INDEX m_h_index
0|1|2|SEARCH TABLE h AS h USING INTEGER PRIMARY KEY (rowid=?)
....

Yuri

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite scans the largest table in the query where a very small table is available

Richard Hipp-3
In reply to this post by Yuri
On 1/20/16, Yuri <[hidden email]> wrote:
>
> Does SQLite optimizer look at the row counts?

It does if they are available.  Run ANALYZE to generate the row counts.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite scans the largest table in the query where a very small table is available

Yuri
In reply to this post by Hick Gunter
On 01/20/2016 09:21, Hick Gunter wrote:
> ave you tried running ANALYZE on a representative dataset? This will determine the "shape" of your tables and allow the query planner to make better guesses of the costs associated with each join.
>
> If, after ANALYZE, you still feel the need to improve over the query planners' ordering, you can still use CROSS JOIN to force a certain order of tables.
ANALYZE helped, thanks!

Yuri
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users