Re: [PERFORM] Finalizing commit taking very long
Hello Tom, I can confirm that adding the indexes used by the deferred constraint triggers solved the issue. Thank you very much for your suggestions. Best regards, Giulio Cesare On 10/24/07, Giulio Cesare Solaroli [EMAIL PROTECTED] wrote: On 10/24/07, Tom Lane [EMAIL PROTECTED] wrote: Giulio Cesare Solaroli [EMAIL PROTECTED] writes: How can I try to isolate the trigger taking so long, in oder to understand which is/are the missing index(es)? Try SET CONSTRAINTS ALL IMMEDIATE and then EXPLAIN ANALYZE the delete. This should cause all the triggers to run within the scope of the EXPLAIN ANALYZE, and you'll be able to see which one(s) are slow. (This assumes you're running a recent release of PG; I think EXPLAIN shows trigger times since 8.1 or so.) I was thinking about something similar after writing the last message. Thank you very much for your attention!! Giulio Cesare ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] Finalizing commit taking very long
On 10/24/07, Tom Lane [EMAIL PROTECTED] wrote: Giulio Cesare Solaroli [EMAIL PROTECTED] writes: How can I try to isolate the trigger taking so long, in oder to understand which is/are the missing index(es)? Try SET CONSTRAINTS ALL IMMEDIATE and then EXPLAIN ANALYZE the delete. This should cause all the triggers to run within the scope of the EXPLAIN ANALYZE, and you'll be able to see which one(s) are slow. (This assumes you're running a recent release of PG; I think EXPLAIN shows trigger times since 8.1 or so.) I was thinking about something similar after writing the last message. Thank you very much for your attention!! Giulio Cesare ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Newbie question about degraded performance on delete statement.
Hello Gregory, On 10/3/07, Greg Williamson [EMAIL PROTECTED] 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 5: don't forget to increase your free space map settings
Re: [PERFORM] Newbie question about degraded performance on delete statement. (SOLVED)
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 [EMAIL PROTECTED] wrote: Hello Gregory, On 10/3/07, Greg Williamson [EMAIL PROTECTED] 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
[PERFORM] Newbie question about degraded performance on delete statement.
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. 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. Given this evidence, what are the best practices to fix/avoid this kind of problems? I am using PostgreSQL 8.1.4 both on Linux (on a Parallels virtual machine with a Linux OS) and on Solaris, on a hosted zone; the Solaris version is running the live DB, while the Linux instance is on my development machine using a snapshot of the live data. Thanks for your attention. Best regards, Giulio Cesare Solaroli ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster