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
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 |