Hum... What such thing strange. Hash algorithms should be better than BTREE and RTREE algorithms. It is a very interisting thing an postgres, isnt't? Sorry by my useless help.
> -----Mensagem original----- > De: Daryl Herzmann [mailto:[EMAIL PROTECTED]] > Enviada em: quarta-feira, 24 de julho de 2002 12:46 > Para: Elielson Fontanezi > Cc: pgsql-sql; pgsql-general > Assunto: Re: RES: [SQL] Queries not using Index > > > 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 > > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
