Re: temporary tables, indexes, and query plans

Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> writes:
> The sequence goes exactly like this:

> BEGIN;
> CREATE TEMPORARY TABLE (20 columns, mostly text, a few int).
> COPY (approx 8 million rows, ~900 MB)[1]
> UPDATE (2.8 million of the rows)
> UPDATE (7 rows)
> UPDATE (250 rows)
> UPDATE (3500 rows)
> UPDATE (3100 rows)
> a bunch of UPDATE (1 row)
> ...

> Experimentally, I noticed that performance was not especially great.
> So, I added some indexes (three indexes on one column each). One index
> is UNIQUE.
> The first UPDATE can't use any of the indexes. The rest should be able to.

Please ... there is *nothing* exact about that. It's not even clear
what the datatypes of the indexed columns are, let alone what their
statistics are, or whether there's something specific about how you're
declaring the table or the indexes.

Here's an exact test case, which is something I just tried to see if
I could easily reproduce your results:

begin;
create temp table foo (f1 int, f2 text, f3 text);
insert into foo select x, 'xyzzy', x::text from generate_series(1,1000000) x;
update foo set f2 = 'bogus' where f1 < 500000;
explain update foo set f2 = 'zzy' where f1 = 42;
create index fooi on foo(f1);
explain update foo set f2 = 'zzy' where f1 = 42;
analyze foo;
explain update foo set f2 = 'zzy' where f1 = 42;
rollback;

I get a seqscan, a bitmap index scan, then a plain indexscan, which
is about what I'd expect. Clearly there's something you're doing
that deviates from this, but you are failing to provide the detail
necessary to figure out what the critical difference is.

regards, tom lane