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 6ABE9D1BB64 for ; Wed, 21 Apr 2004 11:31:25 -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 79866-10 for ; Wed, 21 Apr 2004 11:31:27 -0300 (ADT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 44F9FD1BABD for ; Wed, 21 Apr 2004 11:31:23 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 380D935A6C; Wed, 21 Apr 2004 07:31:27 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 3672F35A68; Wed, 21 Apr 2004 07:31:27 -0700 (PDT) Date: Wed, 21 Apr 2004 07:31:27 -0700 (PDT) From: Stephan Szabo To: Edoardo Ceccarelli Cc: Christopher Kings-Lynne , Nick Barr , pgsql-performance@postgresql.org Subject: Re: slow seqscan In-Reply-To: <408641B7.5090808@axa.it> Message-ID: <20040421072156.K55057@megazone.bigpanda.com> References: <40862003.2090201@axa.it> <4086272E.2080102@chuckie.co.uk> <40863228.907@axa.it> <408636A5.2050305@familyhealth.com.au> <408641B7.5090808@axa.it> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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/349 X-Sequence-Number: 6649 On Wed, 21 Apr 2004, Edoardo Ceccarelli wrote: > > > 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 That's a 7.4 feature I think (and I think the version with two columns may need extra parens around the lower()). I think the only way to do something equivalent in 7.3 is to make a function that concatenates the two in some fashion after having applied the lower to the one part and then using that in the queries as well. Plus, if you're not in "C" locale, I'm not sure that it'd help in 7.3 anyway. > >> 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? It's a question of how many pages it thinks it's going to have to retrieve in order to handle the request. If it say needs (or think it needs) to retrieve 50% of the pages, then given a random_page_cost of 4, it's going to expect the index scan to be about twice the cost. Generally speaking one good way to compare is to try the query with explain analyze and then change parameters like enable_seqscan and try the query with explain analyze again and compare the estimated rows and costs. That'll give an idea of how it expects the two versions of the query to compare speed wise.