Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 68BB4476391 for ; Wed, 22 Jan 2003 13:15:07 -0500 (EST) Received: from martin.sysdetect.com (martin.sysdetect.com [65.209.102.1]) by postgresql.org (Postfix) with ESMTP id D5F28476E57 for ; Wed, 22 Jan 2003 12:45:21 -0500 (EST) Received: (from mail@localhost) by martin.sysdetect.com (8.11.3/8.11.3) id h0MHjPJ23547 for ; Wed, 22 Jan 2003 17:45:25 GMT Received: from winwood.sysdetect.com(172.16.1.1) via SMTP by mail.sysdetect.com, id smtpdZ15586; Wed Jan 22 17:45:24 2003 Received: from winwood.sysdetect.com (seth@localhost) by winwood.sysdetect.com (8.11.6/8.11.6) with ESMTP id h0MHjOs14488 for ; Wed, 22 Jan 2003 12:45:24 -0500 Message-Id: <200301221745.h0MHjOs14488@winwood.sysdetect.com> From: Seth Robertson To: pgsql-performance@postgresql.org Subject: Re: Postgres 7.3.1 poor insert/update/search performance References: <200301212120.h0LLKn820463> <20030121134242.Q84028-100000@megazone23.bigpanda.com> <200301212207.h0LM7Dt21080@winwood.sysdetect.com> <13165.1043188295@sss.pgh.pa.us> Comments: In reply to a message from "Seth Robertson " dated "Wed, 22 Jan 2003 02:19:45 -0500." Date: Wed, 22 Jan 2003 12:45:24 -0500 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/224 X-Sequence-Number: 871 Seth Robertson writes: However, it looks like my hope to use arrays is doomed though, I'm not sure I can handle the performance penalty. Just in case I get the person who implemented arrays annoyed or worried, I did not properly modify the "array" test and was vacuum'ing the wrong table every 10000 vectors during the test. I realized that this morning and the new array results are listed below. I also experimented with batching read operations, and I was surprised to find that this helps a great deal as well. ---------------------------------------------------------------------- "normal" C libpq 256 t/s "normal" Perl DBI 251 t/s "DBI Prepared Statement" Perl DBI 254 t/s "Batching" Perl DBI 1149 t/s "arrays" Perl DBI 250 t/s (*) "arrays with batching" Perl DBI 1020 t/s (*) "server-side function" Perl DBI 84 t/s "server-side trigger" Perl DBI 84 t/s "normal" Perl DBI read 1960 t/s "batched" Perl DBI read 3076 t/s (*) "array" Perl DBI read 1754 t/s (*) "batched array" Perl DBI read 2702 t/s (*) "normal" Perl DBI for Oracle 314 t/s "normal" Perl DBI read for Oracle 395 t/s ---------------------------------------------------------------------- (*) New/updated from this morning This brings array code to within 11% of the performance of batched non-arrays, and close enough to be an option. I may well be doing something wrong with the server-side functions, but I don't see anything quite so obviously wrong. -Seth Robertson