Re: temporary tables, indexes, and query plans

On Wed, Oct 27, 2010 at 4:45 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

The indexed data types are:
- an INT (this is a unique ID, and it is declared so)
- two TEXT fields. The initial value of one of the text fields is
NULL, and it is updated to be not longer than 10 characters long. The
other text field is not more than 4 characters long. My guesstimate as
to the distribution of values in this column is not more than 2 dozen.

I am not doing anything when I define the table except using TEMPORARY.
The indexes are as bog-standard as one can get. No where clause, no
functions, nothing special at all.

I'd like to zoom out a little bit and, instead of focusing on the
specifics, ask more general questions:

- does the table being temporary effect anything? Another lister
emailed me and wondered if ANALYZE on a temporary table might behave
differently.
- is there some way for me to determine /why/ the planner chooses a
sequential scan over other options? I'm already using auto explain.
- in the general case, are indexes totally ready to use after creation
or is an analyze step necessary?
- do hint bits come into play here at all?

--
Jon