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

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