DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.ar;
	h=Message-ID:Received:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=WduBblIciZ/Af4kThVP0oXYRhfY9PZBbcgEax/DanjU92aSL5remILrjmyO48Nv734hLJNMwXsL55M7pQCe4rZsNp5QMINq7/3fALrYht9J4ctabFzkzEqjW/7/uThg2w1F4q1+u28jfLsnUEdl4kvmBbMh7uHFB4KgUjgQ/ASs=
	;
Message-ID: <20060815154329.12246.qmail@web36108.mail.mud.yahoo.com>
Date: Tue, 15 Aug 2006 15:43:29 +0000 (GMT)
From: =?iso-8859-1?q?Sebasti=E1n=20Baioni?= <sebaioni-postgresql@yahoo.com.ar>
Reply-To: sebaioni-postgresql@yahoo.com.ar
Subject: Re: Inner Join of the same table
To: Performance PostgreSQL <pgsql-performance@postgresql.org>
In-Reply-To: <1155654605.16967.90.camel@archimedes>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi Nark, thanks for your answer.

It's expected to return 1,720 rows (of 80,471 that match with condition WHERE
T.cuiT='12345678901')

We have indexes by :
uesapt000: cuiT, cuiL, PERI;
uesapt001: cuiL, PERI;
uesapt002: cuiT, PERI;

We usually make a vacuum analyze and reindex of every table, and we are running 8.0 and 8.1 for
windows and 7.4 for Linux.

Here is the EXPLAIN:
QUERY PLAN
 1 Unique  (cost=37478647.41..37478650.53 rows=312 width=62)
 2  ->  Sort  (cost=37478647.41..37478648.19 rows=312 width=62)
 3        Sort Key: t.cuiT, t.cuiL, u.maxperi
 4        ->  Merge Join  (cost=128944.78..37478634.48 rows=312 width=62)
 5             Merge Cond: ("outer".cuiL = "inner".cuiL)
 6              Join Filter: (("inner".PERI)::text = "outer".maxperi)
 7              ->  Subquery Scan u  (cost=0.00..37348434.56 rows=3951 width=47)
 8                    ->  GroupAggregate  (cost=0.00..37348395.05 rows=3951 width=25)
 9                          ->  Index Scan using uesapt001 on APORTES  (cost=0.00..37301678.64
rows=9339331 width=25)
10              ->  Sort  (cost=128944.78..129100.44 rows=62263 width=40)
11                    Sort Key: t.cuiL
12                    ->  Index Scan using uesapt002 on APORTES t  (cost=0.00..122643.90
rows=62263 width=40)
13                          Index Cond: (cuiT = '30701965554'::bpchar)

Thanks
     Sebastián Baioni

 --- Mark Lewis <mark.lewis@mir3.com> escribió:

> 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án 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)
> > 
> > I need all the cuiLs whose max(PERI) are from a cuiT, and the Max
> > (FAMI) of those cuiLs, so the sentence is:
> > 
> > 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 = U.cuiL AND T.PERI=U.MAXPERI
> > WHERE T.cuiT='12345678901'
> > 
> > In MS SQL Server it lasts 1minute, in PostgreSQL for Windows it lasts
> > 40minutes and in PostgreSQL for Linux (FreeBSD) it lasts 20minuts.
> > 
> > Do you know if there is any way to tune the server or optimize this
> > sentence?
> > 
> > Thanks
> >      Sebastián Baioni


__________________________________________________
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya! 
http://www.yahoo.com.ar/respuestas