Thread: Delete slow...
Hi, I have a table with 400,000 records, but in delete comand is very slow (4 seconds for operation) Why is very slow? There is some special configuration for this? Ps.: Version = Postgresql 7.4.6 Thanks Diego Ziquinatti
On Tue, 2005-11-29 at 18:13 -0300, Diego wrote:
> Hi,
> I have a table with 400,000 records, but in delete comand is very slow
> (4 seconds for operation)
> Why is very slow? There is some special configuration for this?
>
> Ps.: Version = Postgresql 7.4.6
That smells like a bad design. What do you delete from a 400.000 records
table?
I guess it's ok to be slow.
Mike
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
Thanks by answers... For each record deleted, my postgresql server spended 4 seconds... I know because i have logged each sql instruction, showing the time spended. My question is: is normal this time to a big table (400,000 records) Thanks! Diego Ziquinatti From Brazil (excuse me my english, rsrsrs) Mario Splivalo escreveu: >On Tue, 2005-11-29 at 18:13 -0300, Diego wrote: > > >>Hi, >>I have a table with 400,000 records, but in delete comand is very slow >>(4 seconds for operation) >>Why is very slow? There is some special configuration for this? >> >>Ps.: Version = Postgresql 7.4.6 >> >> > >That smells like a bad design. What do you delete from a 400.000 records >table? > >I guess it's ok to be slow. > > Mike > >
On Tue, 2005-11-29 at 15:32, Diego wrote: > Thanks by answers... > > For each record deleted, my postgresql server spended 4 seconds... > I know because i have logged each sql instruction, showing the time spended. > > My question is: is normal this time to a big table (400,000 records) Do you have any foreign keys involved here? You might need some indexes for faster lookups. And don't worry about your english, I'm sure it's 1,000 times better than most of our Portuguese.
!Hola LISTA!
La duda és :
Yo he hecho un pg_dump de una base de datos llamada CHT
ex: pg_dump cht > cht.backup
después hice un psql (restore) en otro servidor en una base con el mismo nombre
ex: psql cht < cht.backup
Y tengo la mensage:
FATAL: IDENT authentication failed for user "cht_admin"
Mi pg_hba.conf esta definido asi:
LOCAL all all all trust
Mi postgresql 7.3.4 está en linux fedora
Atentamente,
Andréa
--
Esta mensagem foi verificada pelo sistema de antivírus e
acredita-se estar livre de perigo.
Can you provide the output of EXPLAIN ANALYZE for one of these deletes? On Tue, Nov 29, 2005 at 06:32:43PM -0300, Diego wrote: > Thanks by answers... > > For each record deleted, my postgresql server spended 4 seconds... > I know because i have logged each sql instruction, showing the time spended. > > My question is: is normal this time to a big table (400,000 records) > > Thanks! > Diego Ziquinatti > From Brazil (excuse me my english, rsrsrs) > > Mario Splivalo escreveu: > > >On Tue, 2005-11-29 at 18:13 -0300, Diego wrote: > > > > > >>Hi, > >>I have a table with 400,000 records, but in delete comand is very slow > >>(4 seconds for operation) > >>Why is very slow? There is some special configuration for this? > >> > >>Ps.: Version = Postgresql 7.4.6 > >> > >> > > > >That smells like a bad design. What do you delete from a 400.000 records > >table? > > > >I guess it's ok to be slow. > > > > Mike > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
A good idea would be to:
1) if your spanish is strong enough, post into the -es mailing list and kindly request for help there.
(Si tu espaniol es fuerte, escribi a la lista -es, te vas a encontrar mas comodo con las posibles respuestas)
2) if your spanish isn't strong enough, post here again, but show us:
a) The SQL statement being used [aka the query]
b) The description of the tables involved [in your pgsql shell `\d tablename`
c) Your server specs (OS, version, hd, ram, cpu,etc)
If you usually run this query, and it works faster you may be facing a load in the database or in the server, or in both at the same time. Can you ensure if this is not the case?
3) if your spanish is poor, and your english is poor, but you have enough time, then teach us portugues :)
Best regards,
Guido Barosio
Camberley, Surrey
United Kingdom
On 11/29/05, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
On Tue, 2005-11-29 at 15:32, Diego wrote:
> Thanks by answers...
>
> For each record deleted, my postgresql server spended 4 seconds...
> I know because i have logged each sql instruction, showing the time spended.
>
> My question is: is normal this time to a big table (400,000 records)
Do you have any foreign keys involved here? You might need some indexes
for faster lookups.
And don't worry about your english, I'm sure it's 1,000 times better
than most of our Portuguese.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
"Adopting the position that you are smarter than an automatic
optimization algorithm is generally a good way to achieve less
performance, not more" - Tom Lane.