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:akrherz(at)iastate(dot)edu]
> 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
>
>