From: Mark Lewis <mark.lewis@mir3.com>
To: sebaioni-postgresql@yahoo.com.ar
Cc: Performance PostgreSQL <pgsql-performance@postgresql.org>
Subject: Re: Inner Join of the same table
Date: Tue, 15 Aug 2006 08:10:05 -0700
Message-ID: <1155654605.16967.90.camel@archimedes> (raw)
In-Reply-To: <20060815143812.69494.qmail@web36109.mail.mud.yahoo.com>
References: <20060815143812.69494.qmail@web36109.mail.mud.yahoo.com>
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
>
> Instrumentos musicalesSebastián Baioni Ofertas náuticas
>
>
> ______________________________________________________________________
> Preguntá. Respondé. Descubrí.
> Todo lo que querías saber, y lo que ni imaginabas,
> está en Yahoo! Respuestas (Beta).
> Probalo ya!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.lewis@mir3.com, sebaioni-postgresql@yahoo.com.ar
Subject: Re: Inner Join of the same table
In-Reply-To: <1155654605.16967.90.camel@archimedes>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox