Re: Inner Join of the same table

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