pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance on query
2+ messages / 2 participants
[nested] [flat]
* performance on query
@ 2005-10-26 23:47 Sidar López Cruz <sidarlopez@hotmail.com>
2005-10-27 03:00 ` Re: performance on query Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 2+ messages in thread
From: Sidar López Cruz @ 2005-10-26 23:47 UTC (permalink / raw)
To: pgsql-performance
I DON'T KNOW WHAT TO DO WITH THIS QUERYS...
Comparation with sql server, sql server wins !!!
Table sizes:
archivos: 40MB
fotos: 55MB
select count(1) from fotos f where not exists (select a.archivo from
archivos a where a.archivo=f.archivo)
173713 ms.
110217 ms.
83122 ms.
select count(*) from
(
select archivo from fotos
except
select archivo from archivos
) x;
201479 ms.
SELECT count(*)
FROM fotos f
LEFT JOIN archivos a USING(archivo)
WHERE a.archivo IS NULL
199523 ms.
_________________________________________________________________
MSN Amor: busca tu ½ naranja http://latam.msn.com/amor/
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: performance on query
2005-10-26 23:47 performance on query Sidar López Cruz <sidarlopez@hotmail.com>
@ 2005-10-27 03:00 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 2+ messages in thread
From: Jim C. Nasby @ 2005-10-27 03:00 UTC (permalink / raw)
To: Sidar L?pez Cruz <sidarlopez@hotmail.com>; +Cc: pgsql-performance
So the issue is that instead of taking 174 seconds the query now takes
201?
I'm guessing that SQL server might be using index covering, but that's
just a guess. Posting query plans (prefferably with actual timing info;
EXPLAIN ANALYZE on PostgreSQL and whatever the equivalent would be for
MSSQL) might give us some idea.
On Wed, Oct 26, 2005 at 05:47:31PM -0600, Sidar L?pez Cruz wrote:
> I DON'T KNOW WHAT TO DO WITH THIS QUERYS...
> Comparation with sql server, sql server wins !!!
>
>
> Table sizes:
> archivos: 40MB
> fotos: 55MB
>
> select count(1) from fotos f where not exists (select a.archivo from
> archivos a where a.archivo=f.archivo)
> 173713 ms.
> 110217 ms.
> 83122 ms.
>
> select count(*) from
> (
> select archivo from fotos
> except
> select archivo from archivos
> ) x;
> 201479 ms.
>
> SELECT count(*)
> FROM fotos f
> LEFT JOIN archivos a USING(archivo)
> WHERE a.archivo IS NULL
> 199523 ms.
>
> _________________________________________________________________
> MSN Amor: busca tu ? naranja http://latam.msn.com/amor/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-10-27 03:00 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-26 23:47 performance on query Sidar López Cruz <sidarlopez@hotmail.com>
2005-10-27 03:00 ` Jim C. Nasby <jnasby@pervasive.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