X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3C149D1B4F0 for ; Mon, 13 Oct 2003 10:28:16 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 67331-06 for ; Mon, 13 Oct 2003 07:27:28 -0300 (ADT) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 99700D1B4E1 for ; Mon, 13 Oct 2003 07:27:24 -0300 (ADT) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id h9DAXdSg023601 for ; Mon, 13 Oct 2003 16:03:39 +0530 Received: from persistent.co.in (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by persistent.co.in (8.12.9/8.12.9) with ESMTP id h9DAXawc023545; Mon, 13 Oct 2003 16:03:38 +0530 Message-ID: <3F8A7E0B.8040202@persistent.co.in> Date: Mon, 13 Oct 2003 15:57:23 +0530 From: Shridhar Daithankar Organization: Persistent Systems Pvt. Ltd. User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5b) Gecko/20030917 Thunderbird/0.3a X-Accept-Language: en-us, en MIME-Version: 1.0 To: David Griffiths Cc: pgsql-performance@postgresql.org Subject: Re: Another weird one with an UPDATE References: <052001c39030$1ccc8020$6501a8c0@griffiths2> <05a401c3903f$a8524850$6501a8c0@griffiths2> <20031011153251.F57866@megazone.bigpanda.com> <080d01c3912a$1356dea0$6501a8c0@griffiths2> <20031012184536.N5307@megazone.bigpanda.com> <098b01c39152$921632e0$6501a8c0@griffiths2> In-Reply-To: <098b01c39152$921632e0$6501a8c0@griffiths2> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/417 X-Sequence-Number: 4165 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