Message-ID: <B52A6C218D2145378337CFDC89699D99@andrusnotebook>
From: "Andrus" <kobruleht2@hot.ee>
To: <pgsql-performance@postgresql.org>,
	"PFC" <lists@peufeu.com>
References: <gh38cg$1544$1@news.hub.org> <op.uljfbad8cigqcu@soyouz>
In-Reply-To: <op.uljfbad8cigqcu@soyouz>
Subject: Re: analyzing intermediate query
Date: Tue, 2 Dec 2008 16:00:48 +0200
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="utf-8"; reply-type=response
Content-Transfer-Encoding: 7bit

> Generally if you know your temptbl will always contains a few rows (say, 
> generally a few and never more than a few thousands) it is better to use 
> something like that :
>
> - get list of items
> - SELECT * FROM table WHERE id IN (...)

My list can contain 1 .. 100000  records and table contains 3000000 records 
and is growing.

As discussed here few time ago, IN (...)  forces seq scan over 3000000 rows 
and maybe stack overflow exception also occurs (stack overflow occurs in 
8.0, maybe it is fixed in 8.1).

Using temp table + ANALYZE enables bitmap index scan for this query and is 
thus a lot faster.

I formerly used IN (...) but changed this to use temp table + primary key on 
temp table + analyze this temp table.

Using 8.1.4

I can switch this to temp table also if it helps.
This requires some special logic to generate temp table name since there may 
be a number of such tables in single transaction, so is would be major appl 
rewrite.

Andrus.