[sqlite] Indexes not being used after INNER JOINS?

[Hugo Ferreira]

Hi everyone,

I seem to be having a problem here with LEFT JOINS between tables and
results of INNER JOINS. Take for example the following example (table
definition is in the end):

TABLE COUNT esparqueologico: 750
TABLE COUNT data: 3828
TABLE COUNT reftemporal: 3972

This query would take 6.7s to run (750 rows):

select * from esparqueologico oe left join
  (data d cross join reftemporal r on d.reftemporal_id = r.id) x
on oe.datacao_id = x.id

However this takes virtually zero time (750 rows):

select * from esparqueologico oe left join data d on oe.datacao_id = d.id

And this takes 0.1s (3828 rows):

select * from data d inner join reftemporal r on d.reftemporal_id = r.id

It seems to me that indexes are lost in the first query. Here is the data
definition I'm using:

CREATE TABLE data (
    id guid NOT NULL,
    reftemporal_id guid NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (reftemporal_id) REFERENCES reftemporal(id),
)

CREATE TABLE reftemporal (
    id guid NOT NULL,
    subtype varchar,
    PRIMARY KEY (id)
)

CREATE TABLE esparqueologico (
    id guid NOT NULL,
    datacao_id guid,
    PRIMARY KEY (id),
    FOREIGN KEY (datacao_id) REFERENCES data(id),
)

CREATE INDEX idx_data_reftemporal_id ON data(reftemporal_id)
CREATE INDEX idx_esparqueologico_datacao_id ON esparqueologico(datacao_id)

Cheers,

Hugo Ferreira