pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAnalyze + Index
3+ messages / 2 participants
[nested] [flat]
* Analyze + Index
@ 2002-11-29 04:04 Rudi Starcevic <rudi@oasis.net.au>
2002-11-29 04:45 ` Re: Analyze + Index Rudi Starcevic <rudi@oasis.net.au>
0 siblings, 1 reply; 3+ messages in thread
From: Rudi Starcevic @ 2002-11-29 04:04 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Analyze + Index
2002-11-29 04:04 Analyze + Index Rudi Starcevic <rudi@oasis.net.au>
@ 2002-11-29 04:45 ` Rudi Starcevic <rudi@oasis.net.au>
2002-11-29 10:32 ` Re: Analyze + Index Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Rudi Starcevic @ 2002-11-29 04:45 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Analyze + Index
2002-11-29 04:04 Analyze + Index Rudi Starcevic <rudi@oasis.net.au>
2002-11-29 04:45 ` Re: Analyze + Index Rudi Starcevic <rudi@oasis.net.au>
@ 2002-11-29 10:32 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2002-11-29 10:32 UTC (permalink / raw)
To: Rudi Starcevic <rudi@oasis.net.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-11-29 10:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-29 04:04 Analyze + Index Rudi Starcevic <rudi@oasis.net.au>
2002-11-29 04:45 ` Rudi Starcevic <rudi@oasis.net.au>
2002-11-29 10:32 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox