Hugo Ferreira wrote: > 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)
I'm not an expert, but don't you want an index on reftemporal.id as well? You're querying it in your JOIN clause, but there's no index on the field. -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
