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: (view raw, whole thread or download thread mbox)
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!


> 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!;
> 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 (
> where 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-2017 The PostgreSQL Global Development Group