Re: temporary tables, indexes, and query plans

Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> writes:
> On Wed, Oct 27, 2010 at 5:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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)

OK, so it thinks it can't use the index. (The "cost=10000000000" bit is
the effect of enable_seqscan = off: it's not possible to just never use
seqscans, but we assign an artificially high cost to discourage the
planner from selecting them if there's any other alternative.)

So we're back to wondering why it can't use the index. I will say
once more that we could probably figure this out quickly if you'd
post an exact example instead of handwaving.

regards, tom lane