X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C67A93A451C for ; Thu, 20 Jan 2005 01:36:04 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 95908-05 for ; Thu, 20 Jan 2005 01:35:56 +0000 (GMT) Received: from bast.unixathome.org (bast.unixathome.org [66.11.174.150]) by svr1.postgresql.org (Postfix) with ESMTP id A632F3A456E for ; Thu, 20 Jan 2005 01:35:58 +0000 (GMT) Received: from wocker (wocker.unixathome.org [192.168.0.99]) by bast.unixathome.org (Postfix) with ESMTP id 9482A3D37 for ; Wed, 19 Jan 2005 20:35:57 -0500 (EST) From: "Dan Langille" To: pgsql-performance@postgresql.org Date: Wed, 19 Jan 2005 20:37:59 -0500 MIME-Version: 1.0 Subject: index scan of whole table, can't see why Message-ID: <41EEC527.22215.A7AAF84@localhost> X-mailer: Pegasus Mail for Windows (4.21c) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200501/253 X-Sequence-Number: 9943 Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do see why: a sequential scan of a 130,000 rows. The query goes from 13ms to 1100ms because the of this. The full plans are at http://rafb.net/paste/results/v8ccvQ54.html I have tried some tuning by: set effective_cache_size to 4000, was 1000 set random_page_cost to 1, was 4 The resulting plan changes, but no speed improvment, are at http://rafb.net/paste/results/rV8khJ18.html Any suggestions please? -- Dan Langille : http://www.langille.org/ BSDCan - The Technical BSD Conference - http://www.bsdcan.org/