Обсуждение: Delete slow...

Поиск
Список
Период
Сортировка

Delete slow...

От
Diego
Дата:
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





Re: Delete slow...

От
Mario Splivalo
Дата:
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."



Re: Delete slow...

От
Diego
Дата:
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
>
>


Re: Delete slow...

От
Scott Marlowe
Дата:
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.

Fatal : IDENT authentication

От
"Andrea"
Дата:
!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.

Re: Delete slow...

От
"Jim C. Nasby"
Дата:
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

Re: Delete slow...

От
Guido Barosio
Дата:
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.