Re: temporary tables, indexes, and query plans

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