Postgres Pro
Downloads
Home   >   mailing lists

Thread: Performance in subconsult

Performance in subconsult

From
Sonia Sanchez Diaz
Date:
Hello there!

I have the next query:

psql# select * from table1 t1 where not exists (select * from table2 t2
where t2.id = t1.id);

The execution of this uses more than 90% of my CPU performance and the
answer is slow...

Is there any wrong on my query?

Thanks for the posible answers.

******************************************************************************
    Direccion General de Servicios de Computo Academico
             Productos Interactivos
Sonia Sanchez Diaz
e-mail: sonny@piaget.dgsca.unam.mx
tel: (5)6-22-81-13
ICQ: 127002683
******************************************************************************

Re: Performance in subconsult

From
postgresql@fruru.com
Date:
On Tue, 26 Mar 2002, Sonia Sanchez Diaz wrote:

> psql# select * from table1 t1 where not exists (select * from table2 t2
> where t2.id = t1.id);
>
> The execution of this uses more than 90% of my CPU performance and the
> answer is slow...

Hi,

do you use any indexes on these tables (especially table2.t2) ?

How large are these tables ?

Cheers
Tycho

--
Tycho Fruru            tycho.fruru@conostix.com
Users' impressions of different operating systems, expressed as emoticons:
Linux:   :)
Windows: XP

Re: Performance in subconsult

From
Sonia Sanchez Diaz
Date:

Hello again...

Not, I don't use indexes in the tables, table1 has 1748 records, and
table2 has 41101.

Regards!

Sonny


On Tue, 26 Mar 2002 postgresql@fruru.com wrote:

> On Tue, 26 Mar 2002, Sonia Sanchez Diaz wrote:
>
> > psql# select * from table1 t1 where not exists (select * from table2 t2
> > where t2.id = t1.id);
> >
> > The execution of this uses more than 90% of my CPU performance and the
> > answer is slow...
>
> Hi,
>
> do you use any indexes on these tables (especially table2.t2) ?
>
> How large are these tables ?
>
> Cheers
> Tycho
>
> --
> Tycho Fruru            tycho.fruru@conostix.com
> Users' impressions of different operating systems, expressed as emoticons:
> Linux:   :)
> Windows: XP
>
>
>

Re: Performance in subconsult

From
Justin Clift
Date:
Hi Sonia,

Have you tuned the PostgreSQL memory configuration, or is it still at
it's defaults?

The default memory allocation is really, really, really small, and is
only that way just in case someone loads PostgreSQL onto a server with
almost no memory (like 32MB or something).

The memory configuration is controlled by the postgresql.conf
configuration file, and I think you'll find Bruce Momjian's guide on
PostgreSQL tuning to be very useful :

http://www.ca.postgresql.org/docs/momjian/hw_performance/

:-)

Regards and best wishes,

Justin Clift


Sonia Sanchez Diaz wrote:
>
> Hello there!
>
> I have the next query:
>
> psql# select * from table1 t1 where not exists (select * from table2 t2
> where t2.id = t1.id);
>
> The execution of this uses more than 90% of my CPU performance and the
> answer is slow...
>
> Is there any wrong on my query?
>
> Thanks for the posible answers.
>
> ******************************************************************************
>         Direccion General de Servicios de Computo Academico
>                         Productos Interactivos
> Sonia Sanchez Diaz
> e-mail: sonny@piaget.dgsca.unam.mx
> tel: (5)6-22-81-13
> ICQ: 127002683
> ******************************************************************************
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi