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

Re: BUG #2243: Postgresql fails to finish some queries

From: Andrew - Supernews <andrew+nonews(at)supernews(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2243: Postgresql fails to finish some queries
Date: 2006-02-09 15:12:20
Message-ID: slrndummuk.2i3v.andrew+nonews@atlantis.supernews.net (view raw or flat)
Thread:
Lists: pgsql-bugs
On 2006-02-08, Matej Rizman <matej(dot)rizman(at)gmail(dot)com> wrote:
> No.
>
> But the similar problem first appeared on tables that are on
> production servers and VACUUMed regularly (in addition to autovacuum).
>
> The same problem appears if tables are created with SELECT INTO and
> with indices created latter. COPY is only used in this bug report so
> that I can fill tables with some numbers.
>
> Did you get message in which I found a solution? Parameter work_mem
> has to be changed to 16384 and then postgresql finishes query in 2-3
> seconds. If this parameter is set to default value, it takes about two
> hours to finish this query.

Any time you use a NOT IN (subselect) query, you are pretty much setting
yourself up for performance problems; pg currently doesn't have any way
to plan these queries as joins, so it will plan either as a plain subplan
(in which case the subselect is run once to completion for _every row_ of
the outer query) or as a hashed subplan (in which case the subselect is
run once, stored in a hashtable which is then consulted for each row).

> I though that if work_mem parameter was too small, postgresql would
> extensively use disk. However, this didn't happen in my case - disk
> LEDs blinked only from time to time as under no load.

The "hashed subplan" can only be used if the _estimated_ size of the
subquery result is small enough that the hashtable will fit within work_mem.
If the estimate is larger than this, a hashed subplan will not be used since
it does not spill to disk; instead it will use a plain subplan.

Rewrite the query as an outer join and you will be much better off.

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-02-09 15:24:56
Subject: Re: BUG #2236: extremely slow to get unescaped bytea data
Previous:From: Michael FuhrDate: 2006-02-09 12:39:50
Subject: Re: Please get me out of this ASAP

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