Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 043591337B97 for ; Thu, 28 Oct 2010 11:23:44 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 72378-01-4 for ; Thu, 28 Oct 2010 14:23:36 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 970D81337B92 for ; Thu, 28 Oct 2010 11:23:36 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id o9SENWCR012106; Thu, 28 Oct 2010 10:23:32 -0400 (EDT) To: Jon Nelson cc: pgsql-performance@postgresql.org Subject: Re: temporary tables, indexes, and query plans In-reply-to: References: <4CC864F8.3060801@vmsinfo.com> <7C0800F63CCF4149AC0FC5EE2A041226D52361@sr002-2k3exc.ateb.com> <26190.1288208593@sss.pgh.pa.us> <29004.1288215953@sss.pgh.pa.us> <29671.1288218995@sss.pgh.pa.us> Comments: In-reply-to Jon Nelson message dated "Thu, 28 Oct 2010 09:08:06 -0500" Date: Thu, 28 Oct 2010 10:23:32 -0400 Message-ID: <12105.1288275812@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201010/537 X-Sequence-Number: 40955 Jon Nelson writes: > On Wed, Oct 27, 2010 at 5:36 PM, Tom Lane 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