On 2006-02-08, Matej Rizman <matej.rizman@gmail.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