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

Re: zero performance on query

From: "Dmitri Bichko" <dbichko(at)aveopharma(dot)com>
To: Sidar López Cruz <sidarlopez(at)hotmail(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: zero performance on query
Date: 2005-10-26 05:25:45
Message-ID: F18A6F7CF1661F46920F2CF713122FED46CCEA@mail.aveo.aveopharma.com (view raw or flat)
Thread:
Lists: pgsql-performance
That seems like a pretty horrible way to do that query, given the table sizes.

What about something like:

SELECT count(*)
FROM fotos f
LEFT JOIN archivo a USING(archivo)
WHERE a.archivo IS NULL

Incidentally, can someone explain what the "Materialize" subplan does?  Is this new in 8.1?

Dmitri



> -----Original Message-----
> From: pgsql-performance-owner(at)postgresql(dot)org 
> [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of 
> Sidar López Cruz
> Sent: Wednesday, October 26, 2005 12:27 AM
> To: pgsql-performance(at)postgresql(dot)org
> Subject: [PERFORM] zero performance on query
> 
> 
> what happend with postgresql 8.1b4 performance on query?
> please help me !!!
> 
> look at this:
> select count(*) from fotos where archivo not in (select archivo from 
> archivos)
> Aggregate  (cost=4899037992.36..4899037992.37 rows=1 width=0)
> ->  Seq Scan on fotos  (cost=22598.78..4899037338.07 rows=261716 
> -> width=0)
>        Filter: (NOT (subplan))
>        SubPlan
>          ->  Materialize  (cost=22598.78..39304.22 
> rows=805344 width=58)
>                ->  Seq Scan on archivos  (cost=0.00..13141.44 
> rows=805344 
> width=58)
> 
> I WILL DIE WAITING FOR QUERY RESPONSE !!!
> --
> CREATE TABLE archivos ( archivo varchar(20)) WITHOUT OIDS; 
> CREATE INDEX archivos_archivo_idx  ON archivos  USING 
> btree(archivo); ~800000 rows
> --
> CREATE TABLE fotos
> (
> cedula varchar(20),
> nombre varchar(100),
> apellido1 varchar(100),
> apellido2 varchar(100),
> archivo varchar(20)
> ) WITHOUT OIDS;
> CREATE INDEX fotos_archivo_idx  ON fotos  USING btree (archivo);
> CREATE INDEX fotos_cedula_idx   ON fotos   USING btree (cedula);
> ~500000 rows
> 
> _________________________________________________________________
> Consigue aquí las mejores y mas recientes ofertas de trabajo 
> en América 
> Latina y USA: http://latam.msn.com/empleos/
> 
> 
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer

pgsql-performance by date

Next:From: Christian Paul B. CosinasDate: 2005-10-26 06:05:55
Subject: Configuration Suggestion
Previous:From: Sidar López CruzDate: 2005-10-26 04:26:43
Subject: zero performance on query

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