Thread: Analyze + Index

Hi,

Say I have a table of 3000 user's,
Before I insert another user I need to check if that user is already in 
there, so I have a 3 column index on a table ( phone,lastname,firstname ).

First I check for the user using the index if they are not in there I 
then add an new user so I now have 3001.
For my next user I then check again using the same procedure.

Is the following correct ?

As I have not yet Analyzed the db my index is still 3000 rows and my 
user table is 3001.
If I add another user my index is still 3000 and my table 3002.

So I either analyze after each insert or use a seqential scan.

I wonder this as I have a csv file of users to bulk insert each day.
Typically I'll have a file of 1500 or so  users to add or append data to.

Thanks.

Hi,

I think I've found what I need to know.From techdocs.postgresql.org
>> Every you run an INSERT, UPDATE, or DELETE on an indexed table, 
PostgreSQL must update all of the table's indexes to reflect the new 
data (unlike some>> RDBMSs, PostgreSQL updates indexes immediately so they never get out 
of synch with the data). 

Thanks
Rudi.

On Friday 29 Nov 2002 4:45 am, Rudi Starcevic wrote:
> Hi,
>
> I think I've found what I need to know.
>  From techdocs.postgresql.org
>
>  >> Every you run an INSERT, UPDATE, or DELETE on an indexed table,
>
> PostgreSQL must update all of the table's indexes to reflect the new
> data (unlike some
>
>  >> RDBMSs, PostgreSQL updates indexes immediately so they never get out
> of synch with the data).

Yep - the ANALYSE updates the statistics the planner uses. So - you should
analyse when a noticable portion of your data has changed. If you had 3000
users and batch inserted 1000 users you'd probably want to ANALYSE.

--  Richard Huxton