Re: [GENERAL] Time to insert
Ok, I will try to do a VACUUM ANALYZE in my program.
I suppose it is better to do it too much than too less time
Many thanks for your help Einar.
Renaud
Einar Karttunen wrote:
> On Thu, 3 May 2001, Renaud Thonnart wrote:
>
> > Thank you Einar and Colin for your help.
> >
> > I have some little qusetions more :
> >
> > - How do I interpret a VACUUM ANALYSE ?
> > - I'm going to load version 7.1 : what is the difference between 7.1 and
> > 7.1rc4 ?
> >
> 7.1rc4 is the fourth release candidate. 7.1 is the final release. So you
> should use 7.1.
>
> You just send "VACUUM ANALYZE" command to the backend. The vacuum analyze
> deletes rows, which are marked as unused and recreates the statistics
> about the table. Its use is essential (one time in 2-48h, or after large
> modifications), keeps the database running smoothly. You use vacuum
> analyze as any other command eg (using libpq++):
>
> PgDatabase data;
>
> data.ExecTuplesOk("MISC SQL QUERY")
>
> // when you need it
> data.ExecCommandOk("VACUUM ANALYZE");
>
> I think that it is better to do the vacuuming server side, if the
> application doesn't change a large amount of data.
>
> - Einar Karttunen
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Time to insert
On Thu, 3 May 2001, Renaud Thonnart wrote:
> Thank you Einar and Colin for your help.
>
> I have some little qusetions more :
>
> - How do I interpret a VACUUM ANALYSE ?
> - I'm going to load version 7.1 : what is the difference between 7.1 and
> 7.1rc4 ?
>
7.1rc4 is the fourth release candidate. 7.1 is the final release. So you
should use 7.1.
You just send "VACUUM ANALYZE" command to the backend. The vacuum analyze
deletes rows, which are marked as unused and recreates the statistics
about the table. Its use is essential (one time in 2-48h, or after large
modifications), keeps the database running smoothly. You use vacuum
analyze as any other command eg (using libpq++):
PgDatabase data;
data.ExecTuplesOk("MISC SQL QUERY")
// when you need it
data.ExecCommandOk("VACUUM ANALYZE");
I think that it is better to do the vacuuming server side, if the
application doesn't change a large amount of data.
- Einar Karttunen
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Time to insert
Thank you Einar and Colin for your help.
I have some little qusetions more :
- How do I interpret a VACUUM ANALYSE ?
- I'm going to load version 7.1 : what is the difference between 7.1 and
7.1rc4 ?
Renaud THONNART
Einar Karttunen wrote:
> On Thu, 3 May 2001, Renaud Thonnart wrote:
>
> > It is a little difficult to perform VACUUM analyse because I'm writing an
> > application in C++ using libpq++.
> > I Use version 7.0.3
> > I try COPY too but result was about the same.
> >
> int PgConnection::ExecCommandOk("VACUUM ANALYZE");
>
> If you can upgrading to 7.1 it will probably help with the performance
> problem. The copy is good only if you perform very many inserts the
> same time. I use a cron job that performs the vacuuming so I don't
> have to worry about it in client aps. If you have multiple inserts,
> but not so many that you should use a copy, try wrapping them in a
> single transaction.
>
> - Einar Karttunen
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [GENERAL] Time to insert
On Thu, 3 May 2001, Renaud Thonnart wrote:
> It is a little difficult to perform VACUUM analyse because I'm writing an
> application in C++ using libpq++.
> I Use version 7.0.3
> I try COPY too but result was about the same.
>
int PgConnection::ExecCommandOk("VACUUM ANALYZE");
If you can upgrading to 7.1 it will probably help with the performance
problem. The copy is good only if you perform very many inserts the
same time. I use a cron job that performs the vacuuming so I don't
have to worry about it in client aps. If you have multiple inserts,
but not so many that you should use a copy, try wrapping them in a
single transaction.
- Einar Karttunen
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Time to insert
It is a little difficult to perform VACUUM analyse because I'm writing an application in C++ using libpq++. I Use version 7.0.3 I try COPY too but result was about the same. Renaud. Einar Karttunen wrote: > On Thu, 3 May 2001, Renaud Thonnart wrote: > > > Hi everyone > > Could someone explain me why time of insertion become that long when > > table increase ? > > For the same kind of insertion: > > When table is empty : 0.03s/row > > Table has 3663 rows : 2.07s/row > > Is that normal ? > > > > Sincerely, Renaud THONNART > > > Have you performed a VACUUM [ANALYZE]? If you are inserting data in larger > patches consider using copy or dropping indexes. What version of pgsql are > you using? There used to be (if I remember correctly) a bug in 7.1 betas > that made the inserts slow down. > > - Einar Karttunen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Time to insert
On Thu, 3 May 2001, Renaud Thonnart wrote: > Hi everyone > Could someone explain me why time of insertion become that long when > table increase ? > For the same kind of insertion: > When table is empty : 0.03s/row > Table has 3663 rows : 2.07s/row > Is that normal ? > > Sincerely, Renaud THONNART > Have you performed a VACUUM [ANALYZE]? If you are inserting data in larger patches consider using copy or dropping indexes. What version of pgsql are you using? There used to be (if I remember correctly) a bug in 7.1 betas that made the inserts slow down. - Einar Karttunen ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
