Hello again !
I did some changes and got a better plan but it takes for ever, it seems
that sqlite3 can not merge the where clause before the "group by" scan
the whole tables:
===
CREATE VIEW despesas_municipio_orgao_list_view AS
SELECT
a."municipio_id" rowid,
a."ano_exercicio",
c.name as municipio,
b.name as orgao,
count(*) count,
sum(vl_despesa) total,
a."municipio_id",
a."orgao_id"
FROM main."despesas_master" AS a
JOIN municipios_orgaos b ON a.orgao_id=b.id
JOIN municipios c ON a.municipio_id=c.id
LEFT JOIN despesas_detalhe d ON a.id=d.despesa_id
JOIN tipos_despesa e ON d.tp_despesa_id=e.id and e.name='Valor Pago'
GROUP BY municipio_id, orgao_id;
explain query plan
select *
from despesas_municipio_orgao_list_view
where municipio_id=2;
===
Output:
===
selectid|order|from|detail
0|0|0|SCAN TABLE despesas_master AS a USING INDEX
despesas_master_municipio_idx
0|1|1|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY
(rowid=?)
0|2|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE despesas_detalhe AS d USING INDEX
despesas_detalhe_despesa_idx (despesa_id=?)
0|4|4|SEARCH TABLE tipos_despesa AS e USING INTEGER PRIMARY KEY (rowid=?)
===
Executing this takes 3.10414 seconds (the result includes municipio_id=2)
===
select *
from despesas_municipio_orgao_list_view
limit 50
===
Executing this takes 111.839 seconds
===
select *
from despesas_municipio_orgao_list_view
where municipio_id=2;
===
Is this expected to be ok ?
On 11/01/17 19:50, Domingo Alvarez Duarte wrote:
Hello Richard !
It seems that sqlite query plan is having trouble with this query too:
===
create table if not exists municipios(
id integer primary key,
name varchar not null unique collate nocase_slna
);
create table if not exists municipios_orgaos(
id integer primary key,
name varchar not null unique collate nocase_slna
);
create table if not exists tipos_despesa(
id integer primary key,
name varchar not null unique collate nocase_slna
);
CREATE TABLE if not exists despesas_master(
id integer primary key,
ano_exercicio integer not null,
municipio_id integer not null,
orgao_id integer not null,
unique(municipio_id, orgao_id)
);
CREATE TABLE if not exists despesas_detalhe(
id integer primary key,
despesa_id integer not null,
tp_despesa_id integer not null
);
create index if not exists despesas_detalhe_despesa_idx on
despesas_detalhe(despesa_id);
select 'expected good plan';
select '===';
explain query plan
select * from (
SELECT
a."municipio_id",
a."ano_exercicio",
c.name as municipio,
b.name as orgao,
-- count(*) count,
a."orgao_id"
FROM despesas_master AS a
JOIN despesas_detalhe d ON a.id=d.despesa_id
and d.tp_despesa_id=(select id from tipos_despesa where name='any')
JOIN municipios c ON a.municipio_id=c.id
JOIN municipios_orgaos b ON a.orgao_id=b.id
--GROUP BY municipio_id, orgao_id
) tbl
WHERE municipio_id=2;
select '';
select 'unexpected bad plan';
select '===';
explain query plan
select * from (
SELECT
a.municipio_id,
a.ano_exercicio,
c.name as municipio,
b.name as orgao,
count(*) count,
a.orgao_id
FROM despesas_master AS a
JOIN despesas_detalhe d ON a.id=d.despesa_id
and d.tp_despesa_id=(select id from tipos_despesa where name='any')
JOIN municipios c ON a.municipio_id=c.id
JOIN municipios_orgaos b ON a.orgao_id=b.id
GROUP BY municipio_id, orgao_id
) tbl
WHERE municipio_id=2;
===
sqlite3 < the_above_sql_file
===
expected good plan
===
0|0|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|1|0|SEARCH TABLE despesas_master AS a USING INDEX
sqlite_autoindex_despesas_master_1 (municipio_id=?)
0|2|3|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY
(rowid=?)
0|3|1|SEARCH TABLE despesas_detalhe AS d USING INDEX
despesas_detalhe_despesa_idx (despesa_id=?)
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE tipos_despesa USING COVERING INDEX
sqlite_autoindex_tipos_despesa_1 (name=?)
unexpected bad plan
===
0|0|1|SCAN TABLE despesas_detalhe AS d
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE tipos_despesa USING COVERING INDEX
sqlite_autoindex_tipos_despesa_1 (name=?)
0|1|0|SEARCH TABLE despesas_master AS a USING INTEGER PRIMARY KEY
(rowid=?)
0|2|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY
(rowid=?)
0|0|0|USE TEMP B-TREE FOR GROUP BY
===
On 05/01/17 23:16, Richard Hipp wrote:
On 1/5/17, Domingo Alvarez Duarte <mingo...@gmail.com> wrote:
Hello !
Today I found this unexpected behavior when using sqlite3 trunk:
When using views with joins sqlite3 is choosing expected plans except
for "LEFT JOIN", bellow is the snippet that shows this unexpected
behavior.
===
create table if not exists a(id integer primary key, val text);
create table if not exists b(id integer primary key, a_id integer not
null, val text);
create view if not exists b_view as select b.*, a.* from b left join a
on b.a_id=a.id;
create table if not exists c(id integer primary key, b_id integer not
null, val text);
select 'bad unexpected plan';
explain query plan select c.*, b_view.* from c left join b_view on
c.b_id=b_view.id;
Can you rewrite your query as:
SELECT *
FROM c LEFT JOIN b ON c.b_id=b.id
LEFT JOIN a ON b.id=a.id;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
