Hi!
Thanks for the help. Please see my responses below.
On Wed, 24 Jul 2002, Elielson Fontanezi wrote:
> What kind of index is t2002_06_station_idx?
snet=# select indexdef from pg_indexes where
indexname='t2002_06_station_idx';
indexdef
---------------------------------------------------------------------
CREATE INDEX t2002_06_station_idx ON t2002_06 USING btree (station)
> Have you done this SELECT command below, right?
> select * from t2002_06 WHERE station = 'SAMI4';
Yes.
> This SELECT causes a sequention scan 'cause your index
>is not HASH type, but likely a BTREE one.
> BTREE index is to interval searches (station = 'SAMI4%')
>not precise searchs. (station = 'SAMI4').
I have created similar tables in the past and have never had this INDEX
problem. It was suggested that this 'problem' was a result of the way I
loaded the data into the database. So anyway, I will try your HASH type
idea.
snet=# drop index t2002_06_station_idx;
DROP
snet=# vacuum analyze t2002_06;
VACUUM
snet=# create index t2002_06_station_hash_idx ON t2002_06 USING
hash(station);
CREATE
(((((((((((((( This create took a VERY long time, 40 minutes or so )))
snet=# vacuum analyze t2002_06;
VACUUM
snet=# vacuum analyze;
VACUUM
snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
NOTICE: QUERY PLAN:
Seq Scan on t2002_06 (cost=0.00..35379.69 rows=35526 width=47) (actual
time=20.23..2358.40 rows=38146 loops=1)
Total runtime: 2452.14 msec
EXPLAIN
snet=# set enable_seqscan=off;
SET VARIABLE
snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
NOTICE: QUERY PLAN:
Index Scan using t2002_06_station_hash_idx on t2002_06
(cost=0.00..132190.93 rows=34949 width=47) (actual time=0.14..306.90
rows=38146 loops=1)
Total runtime: 325.22 msec
EXPLAIN
Thanks for the help! I am still reading up on some clustering pointers
and messing with the pg_statistics table. Interesting stuff!
Thanks again,
Daryl
