Re: [PERFORM] Another weird one with an UPDATE
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 dumb. I've found some contradictory advice, and I'm still a bit hazy on how/why Postgres trusts the OS to do caching. I'll post it all tommorow. Merge Join (cost=11819.21..15258.55 rows=12007 width=752) (actual time=4107.64..5587.81 rows=20880 loops=1) Merge Cond: (outer.commercial_entity_id = inner.commercial_entity_id) - Index Scan using comm_serv_comm_ent_id_i on commercial_service cs (cost=0.00..3015.53 rows=88038 width=12) (actual time=0.05..487.23 rows=88038 loops=1) - Sort (cost=11819.21..11846.08 rows=10752 width=740) (actual time=3509.07..3955.15 rows=25098 loops=1) Sort Key: ce.commercial_entity_id - Merge Join (cost=0.00..9065.23 rows=10752 width=740) (actual time=0.18..2762.13 rows=7990 loops=1) Merge Cond: (outer.user_account_id = inner.user_account_id) - Index Scan using user_account_pkey on user_account (cost=0.00..8010.39 rows=72483 width=716) (actual time=0.05..2220.86 rows=72483 loops=1) - Index Scan using comm_ent_usr_acc_id_i on commercial_entity ce (cost=0.00..4787.69 rows=78834 width=24) (actual time=0.02..55.64 rows=7991 loops=1) Total runtime: 226239.77 msec (10 rows) David - Original Message - From: Stephan Szabo [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] 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 still taking a long time. Hmm, does UPDATE user_account SET last_name='abc' 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; give the right results... That might end up being faster. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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 dumb. I've found some contradictory advice, and I'm still a bit hazy on how/why Postgres trusts the OS to do caching. I'll post it all tommorow. Merge Join (cost=11819.21..15258.55 rows=12007 width=752) (actual time=4107.64..5587.81 rows=20880 loops=1) Merge Cond: (outer.commercial_entity_id = inner.commercial_entity_id) - Index Scan using comm_serv_comm_ent_id_i on commercial_service cs (cost=0.00..3015.53 rows=88038 width=12) (actual time=0.05..487.23 rows=88038 loops=1) - Sort (cost=11819.21..11846.08 rows=10752 width=740) (actual time=3509.07..3955.15 rows=25098 loops=1) Sort Key: ce.commercial_entity_id I think this is the problem. Is there an index on ce.commercial_entity_id? - Merge Join (cost=0.00..9065.23 rows=10752 width=740) (actual time=0.18..2762.13 rows=7990 loops=1) Merge Cond: (outer.user_account_id = inner.user_account_id) - Index Scan using user_account_pkey on user_account (cost=0.00..8010.39 rows=72483 width=716) (actual time=0.05..2220.86 rows=72483 loops=1) - Index Scan using comm_ent_usr_acc_id_i on commercial_entity ce (cost=0.00..4787.69 rows=78834 width=24) (actual time=0.02..55.64 rows=7991 loops=1) In this case of comparing account ids, its using two index scans. In the entity field though, it chooses a sort. I think there is an index missing. The costs are also shot up as well. Total runtime: 226239.77 msec Standard performance question. What was the last time these tables/database were vacuumed. Have you tuned postgresql.conf correctly? HTH Shridhar ---(end of broadcast)--- TIP 8: explain analyze is your friend
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(10,0) | not null [snip] Indexes: user_account_pkey primary key btree (user_account_id), Foreign Key constraints: $1 FOREIGN KEY (lang_id) REFERENCES lang(lang_id) ON UPDATE NO ACTION ON DELETE NO ACTION, $2 FOREIGN KEY (source_id) REFERENCES source(source_id) ON UPDATE NO ACTION ON DELETE NO ACTION, $3 FOREIGN KEY (user_role_id) REFERENCES user_role(user_role_id) ON UPDATE NO ACTION ON DELETE NO ACTION 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))? Also, have you run VACUUM ANALYZE lately? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
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 still taking a long time. --- Seq Scan on user_account (cost=0.00..748990.51 rows=36242 width=716) (actual time=10262.50..26568.03 rows=3771 loops=1) Filter: (subplan) SubPlan - Nested Loop (cost=0.00..11.47 rows=1 width=24) (actual time=0.24..0.24 rows=0 loops=72483) - Index Scan using comm_ent_usr_acc_id_i on commercial_entity ce (cost=0.00..4.12 rows=1 width=12) (actual time=0.05..0.05 rows=0 loops=72483) Index Cond: ($0 = user_account_id) - Index Scan using comm_serv_comm_ent_id_i on commercial_service cs (cost=0.00..7.32 rows=3 width=12) (actual time=1.72..1.72 rows=0 loops=7990) Index Cond: (outer.commercial_entity_id = cs.commercial_entity_id) Total runtime: 239585.09 msec (9 rows) Anyone have any thoughts? David ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
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 still taking a long time. --- Seq Scan on user_account (cost=0.00..748990.51 rows=36242 width=716) Do you have an index on user_account.user_account_id? Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
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_entity_id = cs.commercial_entity_id); Unfort, this is still taking a long time. Hmm, does UPDATE user_account SET last_name='abc' 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; give the right results... That might end up being faster. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 UPDATE On Sat, 11 Oct 2003, David Griffiths wrote: Sorry - just found the FAQ ( http://jamesthornton.com/postgres/FAQ/faq-english.html#4.22 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_timestamp; UPDATE user_account SET last_name = 'abc' WHERE EXISTS (SELECT ua.user_account_id FROM user_account ua, commercial_entity ce, commercial_service cs WHERE ua.user_account_id = ce.user_account_id AND ce.commercial_entity_id = cs.commercial_entity_id); SELECT current_timestamp; I don't think that's the query you want. You're not binding the subselect to the outer values of user_account. 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); ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
