pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (6+ messages)

Message-ID: <200410211014.39916.josh@agliodbs.com>
Permalink:  ../200410211014.39916.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200410211014.39916.josh@agliodbs.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: 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