Hello guys I’m doing 1.2 Billion inserts into a table partitioned in 15. When I target the MASTER table on all the inserts and let the trigger decide what partition to choose from it takes 4 hours. If I target the partitioned table directly during the insert I can get 4 times better performance. It takes 1 hour. I’m trying to get more performance while still using the trigger to choose the table, so partitions can be changed without changing the application that inserts the data. What I noticed that iostat is not showing an I/O bottle neck. iostat –xN 1 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util Pgresql--data 0.00 0.00 0.00 8288.00 0.00 66304.00 8.00 60.92 7.35 0.01 4.30 iostat –m 1 Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn dm-2 4096.00 0.00 16.00 0 16 I also don’t see a CPU bottleneck or context switching bottle neck. Postgresql does not seem to write more than 16MB/s or 4K transactions per second unless I target each individual partition. Did anybody have done some studies on partitioning bulk insert performance? Any suggestions on a way to accelerate it ? Running pgsql 9.2.2 on RHEL 6.3 My trigger is pretty straight forward: CREATE OR REPLACE FUNCTION quotes_insert_trigger() RETURNS trigger AS $$ DECLARE tablename varchar(24); bdate varchar(10); edate varchar(10); BEGIN tablename = 'quotes_' || to_char(new.received_time,'YYYY_MM_DD'); EXECUTE 'INSERT INTO '|| tablename ||' VALUES (($1).*)' USING NEW ; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER quotes_insert_trigger BEFORE INSERT ON quotes FOR EACH ROW EXECUTE PROCEDURE quotes_insert_trigger(); Thanks Charles -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
