pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Victor Ciurus <vikcious@gmail.com>
To: pgsql-performance@postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Simple machine-killing query!
Date: Thu, 21 Oct 2004 20:03:41 +0300
Message-ID: <e7a72f40041021100368d91358@mail.gmail.com> (raw)
In-Reply-To: <16017.1098373308@sss.pgh.pa.us>
References: <e7a72f4004102107345063b467@mail.gmail.com>
	<16017.1098373308@sss.pgh.pa.us>

Well guys,

Your replies have been more than helpful to me, showing me both the
learning stuff I still have to get in my mind about real SQL and the
wonder called PostgreSQL and a very good solution from Tom Lane
(thanks a lot sir!)!

Indeed, changing mem_sort and other server parmeters along with the
quite strange (to me!) outer join Tom mentioned finally got me to
finalize the cleaning task and indeed in warp speed (some 5 mintues or
less!). I am running PG v7.4.5 on a PIV Celeron 1,7Ghz with 1,5Gb ram
so talking about the time performance I might say that I'm more than
pleased with the result. As with the amazement PG "caused" me through
its reliability so far I am decided to go even deeper in learning it!

Thanks again all for your precious help!

Regards,
Victor 


> 
> If you are using PG 7.4, you can get reasonable performance out of this
> approach, but you need to jack sort_mem up to the point where the whole
> DIRTY table will fit into sort_mem (so that you get a hashed-subplan
> plan and not a plain subplan).  If you find yourself setting sort_mem to
> more than say half of your machine's available RAM, you should probably
> forget that idea.
> 
> > [explain] select * from bigma,dirty where bigma.email!=dirty.email;
> 
> This of course does not give the right answer at all.
> 
> A trick that people sometimes use is an outer join:
> 
> select * from bigma left join dirty on (bigma.email=dirty.email)
> where dirty.email is null;
> 
> Understanding why this works is left as an exercise for the reader
> ... but it does work, and pretty well too.  If you're using pre-7.4
> PG then this is about the only effective solution AFAIR.
> 
>                         regards, tom lane
>



view thread (6+ messages)  latest in thread

Message-ID: <e7a72f40041021100368d91358@mail.gmail.com>
Permalink:  ../e7a72f40041021100368d91358@mail.gmail.com/
Also on:    postgresql.org/message-id/e7a72f40041021100368d91358@mail.gmail.com

 · 

reply

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: vikcious@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: Simple machine-killing query!
  In-Reply-To: <e7a72f40041021100368d91358@mail.gmail.com>

* 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