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 C0768D1BA8F for ; Wed, 21 Apr 2004 07:10:16 -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 83703-05 for ; Wed, 21 Apr 2004 07:10:12 -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 10781D1BAA2 for ; Wed, 21 Apr 2004 07:10:10 -0300 (ADT) Received: from axa.it (217.221.80.72) by FE-mail03.sfg.albacom.net (7.0.009) id 40757CA50010EF88; Wed, 21 Apr 2004 12:10:03 +0200 Message-ID: <4086487A.4090703@axa.it> Date: Wed, 21 Apr 2004 12:10:02 +0200 From: Edoardo Ceccarelli User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: it, en, en-us MIME-Version: 1.0 Cc: Christopher Kings-Lynne , 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> <408641B7.5090808@axa.it> In-Reply-To: <408641B7.5090808@axa.it> 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/339 X-Sequence-Number: 6639 tried the enable_seqscan = false and I'm having all index scans, timing has improved from 600ms to 18ms wondering what other implications I might expect. Edoardo Ceccarelli ha scritto: > >> 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > >