Re: [PERFORM] Another weird one with an UPDATE

> And what about commercial_entity.user_account_id. Is it indexed and what > is its data type (i.e. does it match numeric(10,0))? Yup - all columns in the statement are indexed, and they are all numeric(10,0). > Also, have you run VACUUM ANALYZE lately? Yup - just before the last run. Will get

Re: [PERFORM] Another weird one with an UPDATE

David Griffiths wrote: Yes, the query operates only on indexed columns (all numeric(10)'s). Column |Type | Modifiers ---+-+-- --- user_account_id | numeric

Re: [PERFORM] Another weird one with an UPDATE

David Griffiths wrote: It's a slight improvement, but that could be other things as well. I'd read that how you tune Postgres will determine how the optimizer works on a query (sequential scan vs index scan). I am going to post all I've done with tuning tommorow, and see if I've done anything dum

Re: [PERFORM] Another weird one with an UPDATE

abo" <[EMAIL PROTECTED]> To: "David Griffiths" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, October 12, 2003 6:48 PM Subject: Re: [PERFORM] Another weird one with an UPDATE > On Sun, 12 Oct 2003, David Griffiths wrote: > > > [snip] > &

Re: [PERFORM] Another weird one with an UPDATE

TED]> Cc: <[EMAIL PROTECTED]> Sent: Sunday, October 12, 2003 6:37 PM Subject: Re: [PERFORM] Another weird one with an UPDATE > David Griffiths wrote: > >>I think you want something like: > >>UPDATE user_account SET last_name = 'abc' > >>

Re: [PERFORM] Another weird one with an UPDATE

On Sun, 12 Oct 2003, David Griffiths wrote: > [snip] > > > I think you want something like: > > UPDATE user_account SET last_name = 'abc' > > WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs > > WHERE user_account.user_account_id = ce.user_account_id AND > > ce.commercial

Re: [PERFORM] Another weird one with an UPDATE

David Griffiths wrote: I think you want something like: UPDATE user_account SET last_name = 'abc' WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs WHERE user_account.user_account_id = ce.user_account_id AND ce.commercial_entity_id = cs.commercial_entity_id); Unfort, this is s

Re: [PERFORM] Another weird one with an UPDATE

[snip] > I think you want something like: > UPDATE user_account SET last_name = 'abc' > WHERE EXISTS (SELECT 1 FROM commercial_entity ce, commercial_service cs > WHERE user_account.user_account_id = ce.user_account_id AND > ce.commercial_entity_id = cs.commercial_entity_id); Unfort, this is st

Re: [PERFORM] Another weird one with an UPDATE

Thanks - that worked. David - Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "David Griffiths" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, October 11, 2003 3:34 PM Subject: Re: [PERFORM] Another weird one with an

Re: [PERFORM] Another weird one with an UPDATE

On Sat, 11 Oct 2003, David Griffiths wrote: > Sorry - just found the FAQ ( > http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22 > ) on how > IN is very slow. > > So I rewrote the query: > > \o ./data/temp.txt > SELECT current_t

Re: [PERFORM] Another weird one with an UPDATE

1)); ... }   Unfort, that will be alot of data moved from Postgres->middle-tier (Weblogic/Resin), which is inefficient.   Anyone see another solution?   David. - Original Message - From: David Griffiths To: [EMAIL PROTECTED] Sent: Saturday, October 11, 2003 12:44

[PERFORM] Another weird one with an UPDATE

I am running an update-query to benchmark various databases; the postgres version is,   UPDATE user_account SET last_name = 'abc'WHERE user_account_id IN (SELECT user_account_id FROM commercial_entity, commercial_service WHERE yw_account_id IS NULLAND commercial_entity.commercial_entity_id =