Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 23C099FB6B5 for ; Mon, 2 Apr 2007 08:26:46 -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 92544-10 for ; Mon, 2 Apr 2007 08:26:37 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 2D49A9FB5B0 for ; Mon, 2 Apr 2007 08:26:42 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from mi0.bluebottle.com (mi0.bluebottle.com [206.188.25.15]) by svr4.postgresql.org (Postfix) with ESMTP id 3664C5AF893 for ; Mon, 2 Apr 2007 11:26:41 +0000 (GMT) Received: from fe0.bluebottle.com (internal.bluebottle.com [206.188.24.43]) by mi0.bluebottle.com (8.13.1/8.13.1) with ESMTP id l32BQYj2010008 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Mon, 2 Apr 2007 04:26:34 -0700 DomainKey-Signature: a=rsa-sha1; s=mail; d=bluebottle.com; c=simple; q=dns; h=received:to:message-id:date:from:subject:references: in-reply-to:mime-version:content-type: content-transfer-encoding:user-agent; b=pveHmzOgikAgTcfCnTh6qnroiKav7INAEpqGM4j/7t5AdrWL2a5xpPucAG2s2Ve3S lyIxLBu3HJ8zIgjSkFeXefA7K2oinjiqZQw/cg3bqg3qMFE4MbzswPkNIsxDMHb Received: from localhost (internal.bluebottle.com [206.188.24.43]) (authenticated bits=0) by fe0.bluebottle.com (8.13.1/8.13.1) with ESMTP id l32BQY4V018216 for ; Mon, 2 Apr 2007 04:26:34 -0700 Received: from alv-global.enator.se (alv-global.enator.se [193.15.240.60]) by mail.bluebottle.com (IMP) with HTTP for ; Mon, 02 Apr 2007 04:26:34 -0700 To: pgsql-admin@postgresql.org Message-ID: <1175513194.4610e86a2ffab@mail.bluebottle.com> Date: Mon, 02 Apr 2007 04:26:34 -0700 From: Rickard =?iso-8859-1?b?U2r2c3Ry9m0=?= Subject: PG does not use my index References: <1175253570.460cf2427984c@mail.bluebottle.com> <20418.1175271262@sss.pgh.pa.us> In-Reply-To: <20418.1175271262@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit User-Agent: Internet Messaging Program (IMP) 3.2.2 X-Virus-Scanned: ClamAV version 0.88.4, clamav-milter version 0.88.4 on localhost X-Virus-Status: Clean X-Trusted-Delivery: <67c4531c5a1d7639fd69c27d07727a07> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.143 tagged_above=0 required=5 tests=BAYES_50=0.001, SPF_FAIL=1.142 X-Spam-Level: * X-Archive-Number: 200704/10 X-Sequence-Number: 25074 Hi! This post is related to the post "Performance of views" but this is another 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 (~40 seconds became 150 ms). BUT when trying on another db (same postgres server 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=0.00..1656.82 rows=426 width=38) (never executed)" ------------------------- slow db: ------------------------- -> Seq Scan on testcase (cost=0.00..2896.42 rows=33242 width=64) (actual time=77.027..791.014 rows=37093 loops=1)" ------------------------- rows=37093 is all of the existing rows of table testcase! (why does it say "never executed"?) Any ideas why it does not use my index??? How can I convince it to do so? /Rickard Citerar Tom Lane : > Rickard =?iso-8859-1?b?U2r2c3Ry9m0=?= > writes: > > I have a view that is really slow and I ca easily work around the > slowness by bypassing the view and query the real table directly. > > Let's see the exact definition of the view and EXPLAIN ANALYZE > results > for doing it both ways. Also, what PG version is this? > > regards, tom lane > ---------------------------------------------------------------------- Need cash? Click to get an emergency loan, bad credit ok http://tags.bluebottle.com/fc/CAaCMPJe8z3dVmZgRfajsIJMkopwDwDI/