X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DCF19D1B470 for ; Thu, 22 Apr 2004 20:13:35 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 66747-09 for ; Thu, 22 Apr 2004 20:13:37 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 9924CD1E104 for ; Thu, 22 Apr 2004 20:13:34 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i3MNDVSx074795 for ; Thu, 22 Apr 2004 23:13:31 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i3MN4EWp073325 for pgsql-performance@postgresql.org; Thu, 22 Apr 2004 23:04:14 GMT From: CoL X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Looking for ideas on how to speed up warehouse loading Date: Fri, 23 Apr 2004 01:05:20 +0200 Organization: Hub.Org Networking Services Lines: 19 Message-ID: References: <40883FAB.3070109@earthlink.net> Reply-To: col@mportal.hu Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7b) Gecko/20040421 X-Accept-Language: en-us, en In-Reply-To: <40883FAB.3070109@earthlink.net> To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/442 X-Sequence-Number: 6742 hi, Sean Shanny wrote, On 4/22/2004 23:56: > > SELECT t1.id, t2.url FROM referral_temp t2 LEFT OUTER JOIN d_referral t1 > ON t2.url = t1.referral_raw_url ORDER BY t1.id index on url (text) has no sense. Try to use and md5 (char(32) column) which contains the md5 hash of url field. and join these ones. You can have a better index on this char 32 field. do not forget to analyze the tables after data load, and you can fine tune you postgresql.conf, default_statistics_target for better index info, and others. check this info pages: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html C.