On Thu, 11 Mar 2004, Octavio Alvarez wrote: > > Hello to everybody. > > I ask your help for a severe problem when doing a query that LEFT JOINs > one table to another ON a field, and then LEFT JOINs again to another > "instance" of a table ON another field which stores the same entity, but > with different meaning. > > I include 3 EXPLAIN ANALYZEs: > * The first one, the target (and problematic) query, which runs in 5 to 6 > minutes. > * The second one, a variation with the second LEFT JOIN commented out, > which runs in 175 to 450 ms. > * The third one, a variation of the first one with ORDER BY removed, which > gives me about 19 seconds. > > Therefore, I feel like there are two problems here the one that raises the > clock to 6 minutes and one that raises it to 20 seconds. I expected a much > lower time. I checked indexes and data types already, they are all fine. > All relevant fields have BTREEs, all PKs have UNIQUE BTREE, and all id and > ext_* fields have 'integer' as data type. Each ext_* has its corresponding > REFERENCES contraint. > > I translated all the table and field names to make it easier to read. I > made my best not to let any typo go through. > > I'd appreciate any help.
This join filter > Join Filter: ("outer".ext_materia__equivalencia = > "outer".id) which I believe belongs to > LEFT JOIN t_materias AS t_materias__equivalentes ON > ext_materia__equivalencia = t_materias.id seems wrong. Did you maybe mean = t_materias__equivalentes.id there? ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]