Hello, thanks to the added info available running the explain plan through pgsl (instead of using pgAdmin) I was able to realize that an (implicitly created) trigger was the culprit of the slowdown I was suffering. Adding an index on the foreign key the trigger was monitoring solved the issue. THANKS EVERYBODY for your kind attention. Best regards, Giulio Cesare On 10/3/07, Giulio Cesare Solaroli <giulio.cesare@xxxxxxxxx> wrote: > Hello Gregory, > > On 10/3/07, Greg Williamson <Gregory.Williamson@xxxxxxxxxxxxxxxx> wrote: > > Giulio Cesare Solaroli wrote: > > > Hello everybody, > > > > > > I have just joined the list, as I am experiencing a degradation on > > > performances on my PostgreSQL instance, and I was looking for some > > > insights on how to fix/avoid it. > > > > > > What I have observed are impossibly high time on delete statements on > > > some tables. > > > > > > The delete statement is very simple: > > > delete from table where pk = ? > > > > > > The explain query report a single index scan on the primary key index, > > > as expected. > > > > > > I have run vacuum using the pgAdmin tool, but to no avail. > > > > > > I have also dropped and recreated the indexes, again without any benefit. > > > > > Make sure you run ANALYZE on the table in question after changes to make > > sure the stats are up to date. > > I have run Analyze (always through the pgAdmin interface), and it did > not provide any benefits. > > > > > I have later created a copy of the table using the "create table > > > table_copy as select * from table" syntax. > > > > > > Matching the configuration of the original table also on the copy > > > (indexes and constraints), I was able to delete the raws from the new > > > table with regular performances, from 20 to 100 times faster than > > > deleting from the original table. > > > > > > > > As another poster indicated, this sounds like foreign constraints where > > the postmaster process has to make sure there are no child references in > > dependent tables; if you are lacking proper indexing on those tables a > > sequential scan would be involved. > > > > Posting the DDL for the table in question and anything that might refer > > to it with an FK relationship would help the list help you. > > clipperz_connection=> \d clipperz.rcrvrs > Table "clipperz.rcrvrs" > Column | Type | Modifiers > ----------------------+--------------------------+----------- > id_rcrvrs | integer | not null > id_rcr | integer | not null > id_prvrcrvrs | integer | > reference | character varying(1000) | not null > header | text | not null > data | text | not null > version | character varying(100) | not null > creation_date | timestamp with time zone | not null > access_date | timestamp with time zone | not null > update_date | timestamp with time zone | not null > previous_version_key | text | not null > Indexes: > "rcrvrs_pkey" PRIMARY KEY, btree (id_rcrvrs) > "unique_rcrvrs_referecnce" UNIQUE, btree (id_rcr, reference) > Foreign-key constraints: > "rcrvrs_id_prvrcrvrs_fkey" FOREIGN KEY (id_prvrcrvrs) REFERENCES > rcrvrs(id_rcrvrs) > "rcrvrs_id_rcr_fkey" FOREIGN KEY (id_rcr) REFERENCES rcr(id_rcr) > DEFERRABLE INITIALLY DEFERRED > > Is this a complete listing of all the DDL involved in defining the > table, or is there something possibly missing here? > > > > > Try running the query with EXPLAIN ANALYZE ... to see what the planner > > says. Put this in a transaction and roll it back if you want to leave > > the data unchanged, e.g. > > BEGIN; > > EXPLAIN ANALYZE DELETE FROM foo WHERE pk = 1234; -- or whatever values > > you'd be using > > ROLLBACK; > > I have already tried the explain plan, but only using the pgAdmin > interface; running it from psql shows some more data that looks very > promising: > > -------------------------------------------------------------------------------------------------------------------- > Index Scan using rcrvrs_pkey on rcrvrs (cost=0.00..3.68 rows=1 > width=6) (actual time=2.643..2.643 rows=1 loops=1) > Index Cond: (id_rcrvrs = 15434) > Trigger for constraint rcrvrs_id_prvrcrvrs_fkey: time=875.992 calls=1 > Total runtime: 878.641 ms > (4 rows) > > The trigger stuff was not shown on the pgAdmin interface. > > I will try to add an index on the foreign key field (id_prvrcrvrs) to > see if this improves performances of the incriminated query. > > Thanks for the kind attention. > > Best regards, > > > Giulio Cesare > ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
