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.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
