On 2006-02-08, Matej Rizman <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
