X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 76D4C9FB274 for ; Tue, 15 Aug 2006 12:10:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 40760-07 for ; Tue, 15 Aug 2006 12:10:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mir3-fs.mir3.com (mail.mir3.com [65.208.188.100]) by postgresql.org (Postfix) with ESMTP id 3DBCC9FB269 for ; Tue, 15 Aug 2006 12:10:07 -0300 (ADT) Received: mir3-fs.mir3.com 172.16.1.11 from 172.16.2.68 172.16.2.68 via HTTP with MS-WebStorage 6.0.6249 Received: from archimedes.mirlogic.com by mir3-fs.mir3.com; 15 Aug 2006 08:10:05 -0700 Subject: Re: Inner Join of the same table From: Mark Lewis To: sebaioni-postgresql@yahoo.com.ar Cc: Performance PostgreSQL In-Reply-To: <20060815143812.69494.qmail@web36109.mail.mud.yahoo.com> References: <20060815143812.69494.qmail@web36109.mail.mud.yahoo.com> Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Organization: MIR3, Inc. Date: Tue, 15 Aug 2006 08:10:05 -0700 Message-Id: <1155654605.16967.90.camel@archimedes> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-27) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.417 tagged_above=0 required=5 tests=AWL, UNPARSEABLE_RELAY, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200608/224 X-Sequence-Number: 20409 Can you provide an EXPLAIN ANALYZE of the query in PG? Have you analyzed the PG database? How many rows is this query expected to return? Which version of PG are you running? What indexes have you defined? -- Mark On Tue, 2006-08-15 at 14:38 +0000, Sebasti=C3=A1n Baioni wrote: > Hello, I'm migrating from MS SQL Server to PostgreSQL 8.1 and I have a > serious problem: > Table: APORTES - Rows: 9,000,000 (9 million) > *cuiT (char 11) > *cuiL (char 11) > *PERI (char 6) > FAMI (numeric 6) >=20 > I need all the cuiLs whose max(PERI) are from a cuiT, and the Max > (FAMI) of those cuiLs, so the sentence is: >=20 > SELECT DISTINCT T.cuiT, T.cuiL. U.MAXPERI, U.MAXFAMI > FROM APORTES T > INNER JOIN > (SELECT cuiL, MAX(PERI) AS MAXPERI, > MAX(FAMI) AS MAXFAMI > FROM APORTES > GROUP BY cuiL) AS U > ON T.cuiL =3D U.cuiL AND T.PERI=3DU.MAXPERI > WHERE T.cuiT=3D'12345678901' >=20 > In MS SQL Server it lasts 1minute, in PostgreSQL for Windows it lasts > 40minutes and in PostgreSQL for Linux (FreeBSD) it lasts 20minuts. >=20 > Do you know if there is any way to tune the server or optimize this > sentence? >=20 > Thanks > Sebasti=C3=A1n Baioni >=20 > Instrumentos musicalesSebasti=C3=A1n Baioni Ofertas n=C3=A1uticas >=20 >=20 > ______________________________________________________________________ > Pregunt=C3=A1. Respond=C3=A9. Descubr=C3=AD. > Todo lo que quer=C3=ADas saber, y lo que ni imaginabas, > est=C3=A1 en Yahoo! Respuestas (Beta). > Probalo ya!=20