Re: temporary tables, indexes, and query plans

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: temporary tables, indexes, and query plans
Date: 2010-10-27 22:36:35
Message-ID: 29671.1288218995@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Jon Nelson <jnelson+pgsql(at)jamponi(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Brad Nicholson 2010-10-27 22:37:23 Re: AIX slow buffer reads
Previous Message Jon Nelson 2010-10-27 22:02:43 Re: temporary tables, indexes, and query plans