Re: Performance Optimization for Dummies 2 - the SQL

On 10/6/06, Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)ca> wrote:
> > how did you determine that it is done every 500 rows? this is the
>
> The import program pages the import table - it is currently set at 500 rows
> per page. With each page, I run an ANALYZE.

right, i just wanted to make sure of something (you are doing it
properly). really, analyze only needs to be run when tables go up an
order of mangitude in size or so, or a little bit less...like when the
table grows 50% or so.

> > default autovacuum paramater. if you followed my earlier
> > recommendations, you are aware that autovacuum (which also analyzes)
> > is not running during bulk inserts, right?

> It's intuitivly obvious, but I can't do bulk inserts. It's just not the
> nature of what we are doing with the data.

right.

> This is currently being done programmatically. The nature of what we're
> doing is suited for imperitive, navigational logic rather than declarative,
> data set logic; just the opposite of what SQL likes, I know! If there's some
> way to replace thousands of lines of analysis and decision trees with
> ultrafast queries - great...
>
> > important feature of analyze is to tell the planner approx. how big
> > the tables are.
>
> But the tables grow as the process progresses - would you not want the
> server to re-evaluate its strategy periodically?

yes, but it makes the most difference when the tables are small so as
to keep the planner from doing seqscans as they grow.

well it looks like you are on the right track, hopefully the process
runs in an acceptable amount of time at this point.

merlin