On Wed, Oct 27, 2010 at 5:36 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Jon Nelson <jnelson+pgsql@xxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
