Re: temporary tables, indexes, and query plans

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

When I place the index creation and ANALYZE right after the bulk
update, follow it with 'set enable_seqscan = false', the next query
(also an UPDATE - should be about 7 rows) results in this plan:

Seq Scan on foo_table (cost=10000000000.00..10000004998.00 rows=24 width=236)

The subsequent queries all have the same first-row cost and similar
last-row costs, and of course the rows value varies some as well. All
of them, even the queries which update exactly 1 row, have similar
cost:

Seq Scan on foo_table (cost=10000000000.00..10000289981.17 rows=1 width=158)

I cranked the logging up a bit, but I don't really know what to fiddle
there, and while I got a lot of output, I didn't see much in the way
of cost comparisons.

--
Jon