X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 19A43D1D206 for ; Wed, 21 Apr 2004 06:41:21 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 81683-02 for ; Wed, 21 Apr 2004 06:41:21 -0300 (ADT) Received: from FE-mail03.sfg.albacom.net (FE-mail03.albacom.net [213.217.149.83]) by svr1.postgresql.org (Postfix) with ESMTP id 6BCF3D1C511 for ; Wed, 21 Apr 2004 06:41:19 -0300 (ADT) Received: from axa.it (217.221.80.72) by FE-mail03.sfg.albacom.net (7.0.009) id 40757CA50010E027; Wed, 21 Apr 2004 11:41:12 +0200 Message-ID: <408641B7.5090808@axa.it> Date: Wed, 21 Apr 2004 11:41:11 +0200 From: Edoardo Ceccarelli User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: it, en, en-us MIME-Version: 1.0 To: Christopher Kings-Lynne Cc: Nick Barr , pgsql-performance@postgresql.org Subject: Re: slow seqscan References: <40862003.2090201@axa.it> <4086272E.2080102@chuckie.co.uk> <40863228.907@axa.it> <408636A5.2050305@familyhealth.com.au> In-Reply-To: <408636A5.2050305@familyhealth.com.au> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/338 X-Sequence-Number: 6638 > What happens if you go: > > CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(rubric, > LOWER(testo)); > > or even just: > > CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(LOWER(testo)); > I wasn't able to make this 2 field index with lower: dba400=# CREATE INDEX annuncio400_rubric_testo_idx ON annuncio400(rubric, LOWER(testo)); ERROR: parser: parse error at or near "(" at character 71 seems impossible to creat 2 field indexes with lower function. The other one does not make it use the index. >> But the strangest thing ever is that if I change the filter with >> another one that represent a smaller amount of data it uses the >> index scan!!! > > > What's strange about that? The less data is going to be retrieved, > the more likely postgres is to use the index. > can't understand this policy: dba400=# SELECT count(*) from annuncio400 where rubric='DD'; count ------- 6753 (1 row) dba400=# SELECT count(*) from annuncio400 where rubric='MA'; count ------- 2165 (1 row) so it's using the index on 2000 rows and not for 6000? it's not that big difference, isn't it? > I suggest maybe increasing the amount of stats recorded for your > rubrik column: > > ALTER TABLE annuncio400 ALTER rubrik SET STATISTICS 100; > ANALYZE annuncio400; > done, almost the same, still not using index > You could also try reducing the random_page_cost value in your > postgresql.conf a little, say to 3 (if it's currently 4). That will > make postgres more likely to use index scans over seq scans. > changed the setting on postgresql.conf, restarted the server, nothing has changed. what about setting this to false? #enable_seqscan = true thanks again Edoardo