Re: Very high effective_cache_size == worse performance?

On Tue, Apr 20, 2010 at 2:03 PM, David Kerr <dmk(at)mr-paradox(dot)net> wrote:

> that thought occured to me while I was testing this. I ran a vacuumdb -z
> on my database during the load and it didn't impact performance at all.
>
> Incidentally the code is written to work like this :
>
> while (read X lines in file){
> Process those lines.
> write lines to DB.
> }
>
> So i would generally expect to get the benefits of the updated staticis
> once the loop ended. no? (would prepared statements affect that possibly?)
>
> Also, while I was debugging the problem, I did load a 2nd file into the DB
> ontop of one that had been loaded. So the statistics almost certinaly
> should
> have been decent at that point.
>
> I did turn on log_min_duration_statement but that caused performance to be
> unbearable,
> but i could turn it on again if it would help.
>
> Dave

You can absolutely use copy if you like but you need to use a non-standard
jdbc driver: kato.iki.fi/sw/db/postgresql/jdbc/copy/. I've used it in the
past and it worked.

Is the whole thing going in in one transaction? I'm reasonably sure
statistics aren't kept for uncommited transactions.

For inserts the prepared statements can only help. For selects they can
hurt because eventually the JDBC driver will turn them into back end
prepared statements that are only planned once. The price here is that that
plan may not be the best plan for the data that you throw at it.

What was log_min_duration_statement logging that it killed performance?

--Nik