Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id C141F9FB255 for ; Thu, 28 Sep 2006 16:11:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 86614-05 for ; Thu, 28 Sep 2006 19:10:58 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 6808B9FB20B for ; Thu, 28 Sep 2006 16:10:58 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 2F25530E07; Thu, 28 Sep 2006 21:10:57 +0200 (MET DST) From: "Carlo Stonebanks" X-Newsgroups: pgsql.performance Subject: Re: Performace Optimization for Dummies Date: Thu, 28 Sep 2006 15:10:49 -0400 Organization: Hub.Org Networking Services Lines: 46 Message-ID: References: <5EE4B6D1-8ECE-4033-A912-DFD9146A5A93@blighty.com> X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-RFC2646: Format=Flowed; Response To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/400 X-Sequence-Number: 21086 > Are you wrapping all this in a transaction? Yes, the transactions can typically wrap 1 to 10 single-table, single-row inserts and updates. > You're doing some dynamically generated selects as part of the > "de-duping" process? They're probably the expensive bit. What > do those queries tend to look like? Without a doubt, this is the expensive bit. > Are you analysing the table periodically? If not, then you might > have statistics based on an empty table, or default statistics, which > might cause the planner to choose bad plans for those selects. Now there's something I didn't know - I thought that analysis and planning was done with every select, and the performance benefit of prepared statements was to plan-once, execute many. I can easily put in a periodic analyse statement. I obviously missed how to use analyze properluy, I thought it was just human-readable output - do I understand correctly, that it can be used to get the SQL server to revaluate its plan based on newer statistics - even on non-prepared queries? > Talking of which, are there indexes on the table? Normally you > wouldn't have indexes in place during a bulk import, but if you're > doing selects as part of the data load process then you'd be forcing > sequential scans for every query, which would explain why it gets > slower as the table gets bigger. There are indexes for every obvious "where this = that" clauses. I don't believe that they will work for ilike expressions. > > Cheers, > Steve > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >