pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance problems on Delete
3+ messages / 2 participants
[nested] [flat]
* performance problems on Delete
@ 2010-08-02 12:34 Silvio Brandani <silvio.brandani@tech.sdb.it>
0 siblings, 0 replies; 3+ messages in thread
From: Silvio Brandani @ 2010-08-02 12:34 UTC (permalink / raw)
To: pgsql-admin
Hi alls,
we have a performance issue on deleting data from a table.
the delete is on the index key so should be very fast but the table
have 13 contraints of type foreign keys and the slowness seems due to
all the works done with the constraint.
Trying to drop the constraint and execute the delete the result is
immediate.
Is there a way to speed up this type of operations??
Any suggestion higly appreciated
Silvio
---
Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/news/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: performance problems on Delete
@ 2010-08-02 12:56 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-03 11:41 ` Re: performance problems on Delete Silvio Brandani <silvio.brandani@tech.sdb.it>
0 siblings, 1 reply; 3+ messages in thread
From: Kevin Grittner @ 2010-08-02 12:56 UTC (permalink / raw)
To: pgsql-admin; silvio.brandani@tech.sdb.it
Silvio Brandani wrote:
> we have a performance issue on deleting data from a table.
>
> the delete is on the index key so should be very fast but the table
> have 13 contraints of type foreign keys and the slowness seems due
> to all the works done with the constraint.
> Trying to drop the constraint and execute the delete the result is
> immediate.
The usual reason for this is that you don't have indexes on the
referencing columns, so to check whether the delete is OK it has to
scan the related tables. Think about what rows need to be checked to
determine whether the delete is allowed and index the columns which
will allow fast access to them.
-Kevin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: performance problems on Delete
2010-08-02 12:56 Re: performance problems on Delete Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-08-03 11:41 ` Silvio Brandani <silvio.brandani@tech.sdb.it>
0 siblings, 0 replies; 3+ messages in thread
From: Silvio Brandani @ 2010-08-03 11:41 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
Kevin Grittner ha scritto:
> Silvio Brandani wrote:
>
>
>> we have a performance issue on deleting data from a table.
>>
>> the delete is on the index key so should be very fast but the table
>> have 13 contraints of type foreign keys and the slowness seems due
>> to all the works done with the constraint.
>> Trying to drop the constraint and execute the delete the result is
>> immediate.
>>
>
> The usual reason for this is that you don't have indexes on the
> referencing columns, so to check whether the delete is OK it has to
> scan the related tables. Think about what rows need to be checked to
> determine whether the delete is allowed and index the columns which
> will allow fast access to them.
>
> -Kevin
>
>
yes,
you where right , an index was missing.
thanks a lot
--
---
Utilizziamo i dati personali che la riguardano esclusivamente per nostre finalità amministrative e contabili, anche quando li comunichiamo a terzi. Informazioni dettagliate, anche in ordine al Suo diritto di accesso e agli altri Suoi diritti, sono riportate alla pagina http://www.savinodelbene.com/news/privacy.html
Se avete ricevuto questo messaggio per errore Vi preghiamo di ritornarlo al mittente eliminandolo assieme agli eventuali allegati, ai sensi art. 616 codice penale http://www.savinodelbene.com/codice_penale_616.html
L'Azienda non si assume alcuna responsabilità giuridica qualora pervengano da questo indirizzo messaggi estranei all'attività lavorativa o contrari a norme.
--
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-08-03 11:41 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-02 12:34 performance problems on Delete Silvio Brandani <silvio.brandani@tech.sdb.it>
2010-08-02 12:56 Re: performance problems on Delete Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-03 11:41 ` Silvio Brandani <silvio.brandani@tech.sdb.it>
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