appendum: Re: *really* simple select doesn't use indices ...

Okay, just bit the bullet, upgraded to v7.1.2, and the problem still
persists:

globalmatch=# vacuum verbose analyze locations;
NOTICE: --Relation locations--
NOTICE: Pages 1395: Changed 0, reaped 0, Empty 0, New 0; Tup 123571: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 76, MaxLen 124; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.11s/0.00u sec.
NOTICE: Index locationstmp_gid_key: Pages 272; Tuples 123571. CPU 0.01s/0.15u sec.
NOTICE: Index locations_zip: Pages 320; Tuples 123571. CPU 0.02s/0.14u sec.
NOTICE: Index locations_country: Pages 342; Tuples 123571. CPU 0.03s/0.13u sec.
NOTICE: --Relation pg_toast_9373225--
NOTICE: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE: Index pg_toast_9373225_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
NOTICE: Analyzing...
VACUUM
globalmatch=# explain SELECT location from locations WHERE zip = '80012';
NOTICE: QUERY PLAN:

Seq Scan on locations (cost=0.00..2939.64 rows=4217 width=16)

EXPLAIN
globalmatch=# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.1.2 on i386-unknown-freebsd4.3, compiled by GCC 2.95.3
(1 row)

On Mon, 28 May 2001, 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)
>
>
>
>

Marc G. Fournier scrappy(at)hub(dot)org
Systems Administrator @ hub.org
scrappy(at){postgresql|isc}.org ICQ#7615664