Skip site navigation (1) Skip section navigation (2)

Re: Inner Join of the same table

From: Sebastián Baioni <sebaioni-postgresql(at)yahoo(dot)com(dot)ar>
To: Performance PostgreSQL <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Inner Join of the same table
Date: 2006-08-15 15:43:29
Message-ID: 20060815154329.12246.qmail@web36108.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(dot)lewis(at)mir3(dot)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


In response to

Responses

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2006-08-15 16:01:24
Subject: Re: setting up foreign keys
Previous:From: Mark LewisDate: 2006-08-15 15:10:05
Subject: Re: Inner Join of the same table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group