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

Re: Simple machine-killing query!

From: Victor Ciurus <vikcious(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Simple machine-killing query!
Date: 2004-10-21 17:03:41
Message-ID: e7a72f40041021100368d91358@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
>

In response to

pgsql-performance by date

Next:From: Josh BerkusDate: 2004-10-21 17:14:39
Subject: Re: Simple machine-killing query!
Previous:From: Steinar H. GundersonDate: 2004-10-21 15:49:23
Subject: Re: Anything to be gained from a 'Postgres Filesystem'?

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