Re: Slow Inserts on 1 table?

Joshua D. Drake wrote:

>> Why can't postgres compile some rough statistics on tables without
>> running analyze?
>
>
> Why can't you just run analyze? You don't have to empty the tables to
> do so and you can alter the statistics on the fly. Heck you can even
> run analyze while doing the inserts.

I shouldn't have to manually run Analyze to make the DB be capable of
handling inserts involving tables with foreign keys correctly.  My code
that is doing the inserts is a java application that works across
multiple DBS - MySQL, PostgreSQL, DB2, MS Access, Sybase, etc.

I shouldn't have to put custom code into it just to make postgres deal
with inserts properly.  No other database that I insert data into has
problems like this.

This will look really nice in the instructions for my data loader -

* - If you are using PostgreSQL for your database server, it has a bug
that causes its performance to become abysmal unless you manually run
this "Analyze" command a little while after you start the load process.

I will have users that don't even know what a database is, much less
have to go out and run manual sysadmin level commands on it to make the
thing work.

>
>> I have already had to disable sequential scans, since the planner is
>> almost _always_ wrong in deciding whether or not to use an index.
>
>
> Sounds again like you are not setting up your statistics correctly or
> running analyze as and when it should.


Doesn't matter if the statistics are perfectly up to date.  It still
doesn't use the indexes.  If the default statistics are so poorly set up
that the planner thinks a 5 million row table scan will be quicker for a
query that is doing exact matches on indexed columns, I would say it is
poorly implemented.  So I just disabled that "feature".  And it works
fine with sequential scans disabled - I have no problem with it in this
respect, since I can turn it off.

Is there any way that I can disable sequential scans for foreign key checks?

Dan


--
****************************
Daniel Armbrust
Biomedical Informatics
Mayo Clinic Rochester
daniel.armbrust(at)mayo.edu
http://informatics.mayo.edu/