From: Andrus <kobruleht2@hot.ee>
To: pgsql-performance@postgresql.org
To: PFC <lists@peufeu.com>
Subject: Re: analyzing intermediate query
Date: Tue, 2 Dec 2008 16:00:48 +0200
Message-ID: <B52A6C218D2145378337CFDC89699D99@andrusnotebook> (raw)
In-Reply-To: <op.uljfbad8cigqcu@soyouz>
References: <gh38cg$1544$1@news.hub.org>
<op.uljfbad8cigqcu@soyouz>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kobruleht2@hot.ee, lists@peufeu.com
Subject: Re: analyzing intermediate query
In-Reply-To: <B52A6C218D2145378337CFDC89699D99@andrusnotebook>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox