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
>
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