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: Wed, 27 Oct 2010 17:45:53 -0400
Message-ID: <29004.1288215953@sss.pgh.pa.us> (raw)
In-Reply-To: <AANLkTing+x8XCwGfzkoWUtoqtBBk0gv9eOVPqQ=O36Dn@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>
Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> The sequence goes exactly like this:
> BEGIN;
> CREATE TEMPORARY TABLE (20 columns, mostly text, a few int).
> COPY (approx 8 million rows, ~900 MB)[1]
> UPDATE (2.8 million of the rows)
> UPDATE (7 rows)
> UPDATE (250 rows)
> UPDATE (3500 rows)
> UPDATE (3100 rows)
> a bunch of UPDATE (1 row)
> ...
> Experimentally, I noticed that performance was not especially great.
> So, I added some indexes (three indexes on one column each). One index
> is UNIQUE.
> The first UPDATE can't use any of the indexes. The rest should be able to.
Please ... there is *nothing* exact about that. It's not even clear
what the datatypes of the indexed columns are, let alone what their
statistics are, or whether there's something specific about how you're
declaring the table or the indexes.
Here's an exact test case, which is something I just tried to see if
I could easily reproduce your results:
begin;
create temp table foo (f1 int, f2 text, f3 text);
insert into foo select x, 'xyzzy', x::text from generate_series(1,1000000) x;
update foo set f2 = 'bogus' where f1 < 500000;
explain update foo set f2 = 'zzy' where f1 = 42;
create index fooi on foo(f1);
explain update foo set f2 = 'zzy' where f1 = 42;
analyze foo;
explain update foo set f2 = 'zzy' where f1 = 42;
rollback;
I get a seqscan, a bitmap index scan, then a plain indexscan, which
is about what I'd expect. Clearly there's something you're doing
that deviates from this, but you are failing to provide the detail
necessary to figure out what the critical difference is.
regards, tom lane
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: <29004.1288215953@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