X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4E41CD1BA46 for ; Fri, 12 Mar 2004 06:09:35 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 96799-04 for ; Fri, 12 Mar 2004 02:09:20 -0400 (AST) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 475EAD1B91C for ; Fri, 12 Mar 2004 02:09:19 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id A93B3356D9; Thu, 11 Mar 2004 22:09:16 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id A7742356C4; Thu, 11 Mar 2004 22:09:16 -0800 (PST) Date: Thu, 11 Mar 2004 22:09:16 -0800 (PST) From: Stephan Szabo To: Octavio Alvarez Cc: pgsql-performance@postgresql.org Subject: Re: Sorting when LEFT JOINING to 2 same tables, even In-Reply-To: <2400.192.168.0.64.1079070086.squirrel@alvarezp.ods.org> Message-ID: <20040311215456.D77015@megazone.bigpanda.com> References: <2400.192.168.0.64.1079070086.squirrel@alvarezp.ods.org> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/158 X-Sequence-Number: 6014 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?