Re: temporary tables, indexes, and query plans

Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> 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.

Well, the autovacuum daemon can't do anything with temp tables, so
you're reliant on doing a manual ANALYZE if you want the planner to
have stats.  Otherwise it should be the same.

> - is there some way for me to determine /why/ the planner chooses a
> sequential scan over other options?

It thinks it's faster, or there is some reason why it *can't* use the
index, like a datatype mismatch.  You could tell which by trying "set
enable_seqscan = off" to see if that will make it change to another
plan; if so, the estimated costs of that plan versus the original
seqscan would be valuable information.

> - in the general case, are indexes totally ready to use after creation
> or is an analyze step necessary?

They are unless you said CREATE INDEX CONCURRENTLY, which doesn't seem
like it's relevant here; but since you keep on not showing us your code,
who knows?

> - do hint bits come into play here at all?

No.

            regards, tom lane