From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org, Victor Ciurus <vikcious@gmail.com>
Subject: Re: Simple machine-killing query!
Date: Thu, 21 Oct 2004 10:14:39 -0700
Message-ID: <200410211014.39916.josh@agliodbs.com> (raw)
In-Reply-To: <e7a72f4004102107345063b467@mail.gmail.com>
References: <e7a72f4004102107345063b467@mail.gmail.com>
Victor,
> [explain] select * from BIGMA where string not in (select * from DIRTY);
> QUERY PLAN
> ------------------------------------------------------------------------
> Seq Scan on bigma (cost=0.00..24582291.25 rows=500 width=145)
> Filter: (NOT (subplan))
> SubPlan
> -> Seq Scan on dirty (cost=0.00..42904.63 rows=2503963 width=82)
This is what you call an "evil query". I'm not surprised it takes forever;
you're telling the database "Compare every value in 2.7 million rows of text
against 2.5 million rows of text and give me those that don't match." There
is simply no way, on ANY RDBMS, for this query to execute and not eat all of
your RAM and CPU for a long time.
You're simply going to have to allocate shared_buffers and sort_mem (about 2GB
of sort_mem would be good) to the query, and turn the computer over to the
task until it's done. And, for the sake of sanity, when you find the
200,000 rows that don't match, flag them so that you don't have to do this
again.
--
Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, vikcious@gmail.com
Subject: Re: Simple machine-killing query!
In-Reply-To: <200410211014.39916.josh@agliodbs.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