appendum: Re: *really* simple select doesn't use indices ... - Mailing list pgsql-hackers

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,
compiledby 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@hub.org
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org                       ICQ#7615664