Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6C2E59F949D for ; Wed, 7 Nov 2007 09:25:46 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 25572-04 for ; Wed, 7 Nov 2007 09:25:43 -0400 (AST) Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id C21E09F9513 for ; Wed, 7 Nov 2007 09:25:42 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id f5so1634032nfh for ; Wed, 07 Nov 2007 05:25:41 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; bh=PDzvKln1yG57p4ohqUKVNiRkj/XuiL3Y8hQwSCxH0uc=; b=fr6QXW6yviaxRWZCTCx5u2tOTpSdqm8oUHUCXfJPKQqRGuJx3lY6oNaa6T9uM2ooV+RykKB4VH+4kQjz7G1NcapkCbUCNBbc91Ve54NgNJBXRRjL52pyryB2ORveIVM0ZDBmURjjh4c4cLBIw4kzOtxTqboMsN5xSwp7Ok4ySD0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=pCVpBs+Q1UfFPcGByAIW8C65Q9pln5X6q9Kfjinz4t7pctGH6/WlorwmAfxCDcB+YwbubamxN/Ujbzd3Zkpauvp+gVvhPkNBPADmBJ4P+UOaupg9G7gpMPWAEK7/D8r+LuT1y9vdlqVL78a3lPkc8NOcj3fX5EHmabjy65LC5rU= Received: by 10.78.170.6 with SMTP id s6mr6059039hue.1194441940786; Wed, 07 Nov 2007 05:25:40 -0800 (PST) Received: by 10.78.202.10 with HTTP; Wed, 7 Nov 2007 05:25:40 -0800 (PST) Message-ID: <88daf38c0711070525r1e7d6daft56970045de0ec8f4@mail.gmail.com> Date: Wed, 7 Nov 2007 14:25:40 +0100 From: "Alexander Staubo" To: "Guillaume Smet" Subject: Re: Estimation problem with a LIKE clause containing a / Cc: pgsql-performance In-Reply-To: <1d4e0c10711070453q214f089cpd144cbb5193d3c4f@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <1d4e0c10711070453q214f089cpd144cbb5193d3c4f@mail.gmail.com> X-Google-Sender-Auth: 1dba036fb7bd908e X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/79 X-Sequence-Number: 27494 On 11/7/07, Guillaume Smet wrote: > While studying a query taking forever after an ANALYZE on a never > analyzed database (a bad estimate causes a nested loop on a lot of > tuples), I found the following problem: [snip] > Total runtime: 31.097 ms [snip] > Total runtime: 31.341 ms [snip] > Total runtime: 34.778 ms > > Which is a really good estimate. That's a difference of less than *three milliseconds* -- a difference probably way within the expected overhead of running "explain analyze". Furthermore, all three queries use the same basic plan: a sequential scan with a filter. At any rate you're microbenchmarking in a way that is not useful to real-world queries. In what way are these timings a problem? Have you tried using an index which supports prefix searches? The text_pattern_ops operator class lets yo do this with a plain B-tree index: create index cms_items_ancestors_index on cms_items (ancestors text_pattern_ops); analyze cms_items; Now all "like 'prefix%'" queries should use the index. Alexander.