Message-ID: <3B13B622.41D25E5@mohawksoft.com>
Date: Tue, 29 May 2001 10:45:54 -0400
From: mlw <markw@mohawksoft.com>
MIME-Version: 1.0
To: "Marc G. Fournier" <scrappy@hub.org>
CC: pgsql-hackers@postgresql.org
Subject: Re: *really* simple select doesn't use indices ...
References: <Pine.BSF.4.33.0105282334270.12563-100000@hub.org>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


This is one my top two problem with Postgres, the seemingly braindead index
selection mechanism.

First, of course try "VACUUM ANALYZE'
Then if the fails, try

set ENABLE_SEQSCAN = off;

Then try your query.


"Marc G. Fournier" wrote:

> First, this is still a v7.1 system ... its totally possible that this is
> long fixed, and I'm way overdue to get it to v7.1.2, which I'll gladly
> accept as a response ...
>
> That said ... seems like a very painful way to arrive at 1 row ... :)
>
> table structure:
>
> globalmatch=# \d locations
>                               Table "locations"
>  Attribute |  Type   |                        Modifier
> -----------+---------+--------------------------------------------------------
>  gid       | integer | not null default nextval('locationstmp_gid_seq'::text)
>  city      | text    |
>  state     | text    |
>  country   | text    |
>  zip       | text    |
>  location  | point   |
> Indices: locations_zip,
>          locationstmp_gid_key
>
> globalmatch=# \d locations_zip
> Index "locations_zip"
>  Attribute | Type
> -----------+------
>  zip       | text
> btree
>
> globalmatch=# EXPLAIN SELECT count(location) from locations WHERE zip = '80012';
> NOTICE:  QUERY PLAN:
>
> Aggregate  (cost=2950.18..2950.18 rows=1 width=16)
>   ->  Seq Scan on locations  (cost=0.00..2939.64 rows=4217 width=16)
>
> EXPLAIN
>
> globalmatch=# SELECT count(location) from locations WHERE zip = '80012';
>  count
> -------
>      1
> (1 row)
>
> globalmatch=# SELECT count(location) from locations;
>  count
> --------
>  123571
> (1 row)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)