Re: Inner Join of the same table

On Tue, Aug 15, 2006 at 03:43:29PM +0000, Sebasti?n Baioni wrote:
> 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)

That's EXPLAIN, not EXPLAIN ANALYZE, which doesn't help us much. Best
bet would be an EXPLAIN ANALYZE from 8.1.x. It would also be useful to
know how MSSQL is executing this query.

If it would serve your purposes, copying the WHERE clause into the
subquery would really help things. I think it might also mean you could
combine everything into one query.

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461