Skip site navigation (1) Skip section navigation (2)

Re: temporary tables, indexes, and query plans

From: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
To:
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: temporary tables, indexes, and query plans
Date: 2010-10-27 18:23:05
Message-ID: AANLkTikJg7gpP5LOqe5DBFk6iORUefyjx8oJ9aobqr2G@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Oct 27, 2010 at 12:59 PM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
> On Wed, Oct 27, 2010 at 12:44 PM, Mladen Gogala
> <mladen(dot)gogala(at)vmsinfo(dot)com> wrote:
>> On 10/27/2010 1:29 PM, Jon Nelson wrote:
>> How big is your default statistics target? The default is rather small, it
>> doesn't produce very good or usable histograms.
>
> Currently, default_statistics_target is 50.

I set it to 500 and restarted postgres. No change in (most of) the query plans!
The update statement that updates 7 rows? No change.
The one that updates 242 rows? No change.
3714? No change.
I killed the software before it got to the 1-row-only statements.

> I'm not trying creating them after the first UPDATE (which updates
> 2.8million of the 10million rows).

I mean to say that I (tried) creating the indexes after the first
UPDATE statement. This did not improve things.
I am now trying to see how creating the indexes before between the
COPY and the UPDATE performs.
I didn't really want to do this because I know that the first UPDATE
statement touches about 1/3 of the table, and this would bloat the
index and slow the UPDATE (which should be a full table scan anyway).
It's every subsequent UPDATE that touches (at most) 4000 rows (out of
10 million) that I'm interested in.

-- 
Jon

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2010-10-27 18:28:02
Subject: Re: CPUs for new databases
Previous:From: Divakar SinghDate: 2010-10-27 18:14:30
Subject: Re: Postgres insert performance and storage requirement compared to Oracle

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group