Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 50F399FB8D8 for ; Tue, 12 Jun 2007 11:04:45 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 52528-04 for ; Tue, 12 Jun 2007 11:04:28 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from cat.bucknet.co.za (cat.bucknet.co.za [196.211.0.10]) by postgresql.org (Postfix) with ESMTP id B83C99FB4C6 for ; Tue, 12 Jun 2007 11:04:28 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by cat.bucknet.co.za (Postfix) with ESMTP id 4CAD32380B7; Tue, 12 Jun 2007 16:03:52 +0200 (SAST) X-Virus-Scanned: by cat at bucknet.co.za Received: from cat.bucknet.co.za ([127.0.0.1]) by localhost (cat.bucknet.co.za [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 1Acueu-mdnNT; Tue, 12 Jun 2007 16:03:47 +0200 (SAST) Received: from fusion.bucknet.co.za (ns3.bucknet.co.za [196.38.143.226]) by cat.bucknet.co.za (Postfix) with ESMTP id F3DD823807F; Tue, 12 Jun 2007 16:03:46 +0200 (SAST) Received: from localhost (unknown [127.0.0.1]) by fusion.bucknet.co.za (Postfix) with ESMTP id 399B6C14036; Tue, 12 Jun 2007 16:04:02 +0200 (SAST) X-Virus-Scanned: by Fusion at bucknet.co.za Received: from fusion.bucknet.co.za ([127.0.0.1]) by localhost (fusion.bucknet.co.za [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id uF0oVKyvbSMO; Tue, 12 Jun 2007 16:04:00 +0200 (SAST) Received: from [10.0.0.6] (unknown [196.25.216.139]) by fusion.bucknet.co.za (Postfix) with ESMTP id 51290C14029; Tue, 12 Jun 2007 16:03:59 +0200 (SAST) Message-ID: <466EA995.7020106@mecola.com> Date: Tue, 12 Jun 2007 16:11:33 +0200 From: Christo Du Preez User-Agent: Thunderbird 1.5.0.10 (X11/20060911) MIME-Version: 1.0 To: Reid.Thompson@ateb.com CC: pgsql-performance@postgresql.org Subject: Re: test / live environment, major performance difference References: <466EA078.20601@mecola.com> <1181655920.19256.3.camel@jhereg> In-Reply-To: <1181655920.19256.3.camel@jhereg> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.24 tagged_above=0 required=5 tests=AWL=-0.106, BAYES_05=-1.11, RCVD_IN_SORBS_WEB=1.456 X-Spam-Level: X-Archive-Number: 200706/205 X-Sequence-Number: 25238 The actual table I noticed the problem has a million rows and it still doesn't use indexing Reid Thompson wrote: > try it with a table with 650K rows... > > On Tue, 2007-06-12 at 15:32 +0200, Christo Du Preez wrote: > >> Good day, >> >> I have noticed that my server never uses indexing. No matter what I do. >> >> As an example I took a table with about 650 rows, having a parentid >> field with an index on parentid. >> >> EXPLAIN ANALYZE >> SELECT * >> FROM layertype >> where parentid = 300; >> >> On my laptop the explain analyze looks like this: >> >> "Index Scan using fki_layertype_parentid on layertype (cost=0.00..8.27 >> rows=1 width=109)" >> " Index Cond: (parentid = 300)" >> >> and on the problem server: >> >> "Seq Scan on layertype (cost=0.00..20.39 rows=655 width=110)" >> " Filter: (parentid = 300)" >> >> ......... >> >> I have dropped the index, recreated it, vacuumed the table, just about >> everything I could think of, And there is just no way I can get the >> query planner to use the index. >> >> PostgreSQL 8.2.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 >> 20061115 (prerelease) (SUSE Linux) >> POSTGIS="1.2.1" GEOS="3.0.0rc4-CAPI-1.3.3" PROJ="Rel. 4.5.0, 22 Oct >> 2006" USE_STATS >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq >> > > > -- Christo Du Preez Senior Software Engineer Mecola IT Mobile: +27 [0]83 326 8087 Skype: christodupreez Website: http://www.locateandtrade.co.za