Re: [PERFORM] Performance Concern

2003-10-27 Thread Manfred Koizar
On Sun, 26 Oct 2003 00:13:36 +0300, Hannu Krosing <[EMAIL PROTECTED]> wrote: >UPDATE baz > SET customer_id = '1234' > WHERE baz_key IN ( >SELECT baz_key > FROM baz innerbaz > WHERE customer_id IS NULL > and innerbaz.baz_key = baz.baz_key > LIMIT 1000 ); AFAICS this is not

Re: [PERFORM] Performance Concern

2003-10-25 Thread Hannu Krosing
John Pagakis kirjutas L, 25.10.2003 kell 12:56: > I wrote a JAVA simulation of the above that did 1000 updates in 37 seconds. > That left me scratching my head because in psql when I did the > semi-equivalent: > > UPDATE baz SET customer_id = '1234' WHERE baz_key IN( SELECT baz_key FROM > baz WHE

Re: [PERFORM] Performance Concern

2003-10-25 Thread John Pagakis
[EMAIL PROTECTED] Sent: Friday, October 24, 2003 11:31 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Performance Concern John, Are you treating each insertion as a separate transaction? If so the performance will suffer. I am doing the same thing in building a data wareh

Re: [PERFORM] Performance Concern

2003-10-25 Thread Greg Stark
"John Pagakis" <[EMAIL PROTECTED]> writes: > UPDATE baz SET customer_id = '1234' WHERE baz_key IN( SELECT baz_key FROM > baz WHERE customer_id IS NULL LIMIT 1000 ); Do an "explain analyze" on this query. I bet it's doing two sequential scans. Unfortunately in 7.3 the WHERE IN type of clause is p

Re: [PERFORM] Performance Concern

2003-10-25 Thread Allen Landsidel
At 05:56 10/25/2003, John Pagakis wrote: Snipping most of this, I only have one suggestion/comment to make. [snip] CREATE TABLE baz ( baz_key int4 NOT NULL, baz_number CHAR(15) NOT NULL, customer_id CHAR(39), foobar_id INTEGER, is_cancelled BOOL DEFAULT false NOT NULL, cr

Re: [PERFORM] Performance Concern

2003-10-25 Thread John Pagakis
is signature generated by ... and I Quote!!(tm) Copyright (c) 1999 SpaZmodic Frog Software, Inc. www.spazmodicfrog.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of John Pagakis Sent: Thursday, October 23, 2003 5:21 AM

Re: [PERFORM] Performance Concern

2003-10-25 Thread Hannu Krosing
John Pagakis kirjutas L, 25.10.2003 kell 10:16: > Christopher - > Thanks. > > Answer 1: > I believe auto commit was off (but I'm not at my dev box right now). I'll > double-check that and the commit interval. > > Answer 2: > Ah ha!! No indexes on FKs. I'll try that. > > Yes, each baz is a uni

Re: [PERFORM] Performance Concern

2003-10-25 Thread John Pagakis
pazmodicfrog.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Christopher Browne Sent: Friday, October 24, 2003 12:11 PM To: [EMAIL PROTECTED] Subject: Re: [PERFORM] Performance Concern [EMAIL PROTECTED] ("John Pagakis") writes: > Greetin

Re: [PERFORM] Performance Concern

2003-10-24 Thread Sean Shanny
hanny [mailto:[EMAIL PROTECTED] Sent: Friday, October 24, 2003 11:31 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Performance Concern John, Are you treating each insertion as a separate transaction? If so the performance will suffer. I am doing the same thing in building

Re: [PERFORM] Performance Concern

2003-10-24 Thread John Pagakis
ailto:[EMAIL PROTECTED] Behalf Of Sean Shanny Sent: Friday, October 24, 2003 11:31 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Performance Concern John, Are you treating each insertion as a separate transaction? If so the performance will suffer. I am doing the same thin

Re: [PERFORM] Performance Concern

2003-10-24 Thread John Pagakis
www.spazmodicfrog.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Rod Taylor Sent: Friday, October 24, 2003 11:23 AM To: [EMAIL PROTECTED] Cc: Postgresql Performance Subject: Re: [PERFORM] Performance Concern On Thu, 2003-10-23 at

Re: [PERFORM] Performance Concern

2003-10-24 Thread Hannu Krosing
Christopher Browne kirjutas R, 24.10.2003 kell 22:10: > That might be something of an improvement, but it oughtn't be > cripplingly different to use a text field rather than an integer. I suspect his slowness comes from not running analyze when it would be time to start using indexes for fk check

Re: [PERFORM] Performance Concern

2003-10-24 Thread Christopher Browne
[EMAIL PROTECTED] ("John Pagakis") writes: > Greetings. > > I have a table that will require 100,000 rows initially. > > Assume the following (some of the field names have been changed for > confidentiality reasons): > > CREATE TABLE baz ( > baz_number CHAR(15) NOT NULL, > customer_id CHAR(

Re: [PERFORM] Performance Concern

2003-10-24 Thread Sean Shanny
John, Are you treating each insertion as a separate transaction? If so the performance will suffer. I am doing the same thing in building a data warehouse using PG. I have to load millions of records each night. I do two different things: 1) If I need to keep the insertions inside the java

Re: [PERFORM] Performance Concern

2003-10-24 Thread Rod Taylor
On Thu, 2003-10-23 at 08:21, John Pagakis wrote: > Greetings. > > I have a table that will require 100,000 rows initially. > > Assume the following (some of the field names have been changed for > confidentiality reasons): > > CREATE TABLE baz ( > baz_number CHAR(15) NOT NULL, > customer

[PERFORM] Performance Concern

2003-10-24 Thread John Pagakis
Greetings. I have a table that will require 100,000 rows initially. Assume the following (some of the field names have been changed for confidentiality reasons): CREATE TABLE baz ( baz_number CHAR(15) NOT NULL, customer_id CHAR(39), foobar_id INTEGER, is_cancelled BOOL DEFAULT fa