pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedzero performance on query
5+ messages / 4 participants
[nested] [flat]
* zero performance on query
@ 2005-10-26 04:26 Sidar López Cruz <sidarlopez@hotmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Sidar López Cruz @ 2005-10-26 04:26 UTC (permalink / raw)
To: pgsql-performance
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: zero performance on query
@ 2005-10-26 05:25 Dmitri Bichko <dbichko@aveopharma.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dmitri Bichko @ 2005-10-26 05:25 UTC (permalink / raw)
To: Sidar López Cruz <sidarlopez@hotmail.com>; 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@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of
> Sidar López Cruz
> Sent: Wednesday, October 26, 2005 12:27 AM
> To: pgsql-performance@postgresql.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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: zero performance on query
@ 2005-10-26 10:30 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Sidar López Cruz <sidarlopez@hotmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Steinar H. Gunderson @ 2005-10-26 10:30 UTC (permalink / raw)
To: pgsql-performance
On Tue, Oct 25, 2005 at 10:26:43PM -0600, Sidar López Cruz wrote:
> 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)
Now, this is interesting; it seems to trigger exactly the same oddity as my
query did (at least one of them; the materialized sequential scan).
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: zero performance on query
@ 2005-10-26 12:05 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 1 reply; 5+ messages in thread
From: Merlin Moncure @ 2005-10-26 12:05 UTC (permalink / raw)
To: Sidar López Cruz <sidarlopez@hotmail.com>; +Cc: pgsql-performance
> 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 !!!
Try:
select count(*) from fotos f where not exists (select archivo from archivos a where a.archivo = f.archivo)
select count(*) from
(
select archivo from fotos
except
select archivo from archivos
);
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: zero performance on query
@ 2005-10-26 15:20 Steinar H. Gunderson <sgunderson@bigfoot.com>
parent: Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 5+ messages in thread
From: Steinar H. Gunderson @ 2005-10-26 15:20 UTC (permalink / raw)
To: pgsql-performance
On Wed, Oct 26, 2005 at 08:05:21AM -0400, Merlin Moncure wrote:
> select count(*) from fotos f where not exists (select archivo from archivos a where a.archivo = f.archivo)
This was an optimization before 7.4, but probably isn't anymore.
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-10-26 15:20 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-26 04:26 zero performance on query Sidar López Cruz <sidarlopez@hotmail.com>
2005-10-26 10:30 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2005-10-26 05:25 Re: zero performance on query Dmitri Bichko <dbichko@aveopharma.com>
2005-10-26 12:05 Re: zero performance on query Merlin Moncure <merlin.moncure@rcsonline.com>
2005-10-26 15:20 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
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