Skip site navigation (1) Skip section navigation (2)

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

From: "Marc G(dot) Fournier" <scrappy(at)hub(dot)org>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: appendum: Re: *really* simple select doesn't use indices ...
Date: 2001-05-29 12:44:34
Message-ID: Pine.BSF.4.33.0105290843370.92287-100000@hub.org (view raw, whole thread or download thread mbox)
Thread:
Lists: 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, 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


In response to

Responses

pgsql-hackers by date

Next:From: Zeugswetter Andreas SBDate: 2001-05-29 12:57:43
Subject: AW: AW: User functions and AIX
Previous:From: D'Arcy J.M. CainDate: 2001-05-29 12:25:24
Subject: Re: AW: User functions and AIX

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group