Re: Performace Optimization for Dummies

On Sep 28, 2006, at 10:53 AM, Carlo Stonebanks wrote:

>> are you using the 'copy' interface?
>
> Straightforward inserts - the import data has to transformed,
> normalised and
> de-duped by the import program. I imagine the copy interface is for
> more
> straightforward data importing. These are - buy necessity - single row
> inserts.

Are you wrapping all this in a transaction?

You're doing some dynamically generated selects as part of the
"de-duping" process? They're probably the expensive bit. What
do those queries tend to look like?

Are you analysing the table periodically? If not, then you might
have statistics based on an empty table, or default statistics, which
might cause the planner to choose bad plans for those selects.

Talking of which, are there indexes on the table? Normally you
wouldn't have indexes in place during a bulk import, but if you're
doing selects as part of the data load process then you'd be forcing
sequential scans for every query, which would explain why it gets
slower as the table gets bigger.

Cheers,
Steve