Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C606C9FA180 for ; Mon, 2 Apr 2007 12:08:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 16726-04 for ; Mon, 2 Apr 2007 12:08:22 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.231]) by postgresql.org (Postfix) with ESMTP id 6C92E9FA0F6 for ; Mon, 2 Apr 2007 12:08:27 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id r28so914330nza for ; Mon, 02 Apr 2007 08:08:26 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=UJrw/A7HZ8yAx9FgfNdm+1B2ePcdyjDT4VqGs4icVLE54lCGmczgw0arA89I9/jmZ814TMyzLFOKgrTQ1Xa14dUWjnGwiTIttrnNrRiits+KQ+pRtLVuQMAZggR+FAXzHjkY04JxZ/TFxgqjGsnkqjt+Gx0vN8bkr7FqXPU5zL8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=iFMO5O0uILhu1A1MZBE7d4KEm28skZgXqQvXbI5olIB3KITmRJkfHmtvfRyJC3Vbkng3U/fa40khwYIfZ1rGKbfI4X1CfnVukb9p5YQvlyA5BnnTqN68Che89xMIdcgE8wV+WKSb1ue7ZpXrjijYatamRme/UQFzzmVqTmECsJA= Received: by 10.114.122.2 with SMTP id u2mr1845123wac.1175526506423; Mon, 02 Apr 2007 08:08:26 -0700 (PDT) Received: by 10.114.36.2 with HTTP; Mon, 2 Apr 2007 08:08:26 -0700 (PDT) Message-ID: Date: Mon, 2 Apr 2007 17:08:26 +0200 From: Federico To: "=?ISO-8859-1?Q?Rickard_Sj=F6str=F6m?=" Subject: Re: PG does not use my index Cc: pgsql-admin@postgresql.org In-Reply-To: <1175513194.4610e86a2ffab@mail.bluebottle.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <1175253570.460cf2427984c@mail.bluebottle.com> <20418.1175271262@sss.pgh.pa.us> <1175513194.4610e86a2ffab@mail.bluebottle.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/15 X-Sequence-Number: 25079 On 4/2/07, Rickard Sj=F6str=F6m wrote: > Hi! > This post is related to the post "Performance of views" but this is anoth= er problem now. > > Problem: PostgreSQL seems to not use my index. > > My postgres is 7.4. > > I started all over again and got my query really fast on one database (~4= 0 seconds became 150 ms). BUT when trying on another db (same postgres serv= er but with approx. 4 times as much data) it was really slow again (say 10 = seconds). > > Then I run the EXPLAIN ANALYSE of a sub-query of my query which I realize= was the problem. > > It seems that it does not make use of the index in the slower database!? > > fast db: > ------------------------- > -> Index Scan using testcase_b_bid_index on testcase (cost=3D0.00..165= 6.82 rows=3D426 width=3D38) (never executed)" > ------------------------- > > > slow db: > ------------------------- > -> Seq Scan on testcase (cost=3D0.00..2896.42 rows=3D33242 width=3D64) = (actual time=3D77.027..791.014 rows=3D37093 loops=3D1)" > ------------------------- > > rows=3D37093 is all of the existing rows of table testcase! > (why does it say "never executed"?) Hi, a full index scan is even more expensive than a sequential scan. So the query optimizer works fine. Regards Federico