pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: temporary tables, indexes, and query plans
Date: Thu, 28 Oct 2010 10:23:32 -0400
Message-ID: <12105.1288275812@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTikmp9Xpf6AOLcKKLuv2_1ZE9X=O_5KXaKKnLk76@mail.gmail.com>
References: <AANLkTinyFRBAR52m7a43-aUuxpLzwirtp5FWwfOYP2vT@mail.gmail.com>
	<4CC864F8.3060801@vmsinfo.com>
	<AANLkTimLX___=XXUMc86tjaxu7ketDpYr2Yd=YJFw0B6@mail.gmail.com>
	<AANLkTikJg7gpP5LOqe5DBFk6iORUefyjx8oJ9aobqr2G@mail.gmail.com>
	<7C0800F63CCF4149AC0FC5EE2A041226D52361@sr002-2k3exc.ateb.com>
	<AANLkTikJp-geSNQyDwEWDvZfjM0195J7PJN5yuRxuJBt@mail.gmail.com>
	<26190.1288208593@sss.pgh.pa.us>
	<AANLkTing+x8XCwGfzkoWUtoqtBBk0gv9eOVPqQ=O36Dn@mail.gmail.com>
	<29004.1288215953@sss.pgh.pa.us>
	<AANLkTimDd_epkFchAy7+OLHcvyMR9utRbG_Ow5GFJ_n+@mail.gmail.com>
	<29671.1288218995@sss.pgh.pa.us>
	<AANLkTikmp9Xpf6AOLcKKLuv2_1ZE9X=O_5KXaKKnLk76@mail.gmail.com>

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



view thread (25+ messages)  latest in thread

Message-ID: <12105.1288275812@sss.pgh.pa.us>
Permalink:  ../12105.1288275812@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/12105.1288275812@sss.pgh.pa.us

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, jnelson+pgsql@jamponi.net
  Subject: Re: temporary tables, indexes, and query plans
  In-Reply-To: <12105.1288275812@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox