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)
