Thread: RES: RES: [SQL] Queries not using Index

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@iastate.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
>
>

Elielson Fontanezi <ElielsonF@prodam.sp.gov.br> writes:
> Hum... What such thing strange.
> Hash algorithms should be better than BTREE and RTREE algorithms.

Perhaps.  The problem with Postgres' hash indexes is that no one has
worked on the hash-index code since Berkeley days (except for one or
two minor bugfixes, I think).  The btree code has gotten a lot more
care and attention, so it is now much better than the hash code.

I'm not sure whether it's really worth anyone's time to try to
bring the hash index code up to speed.  It *might* be better
than btree for certain limited applications, if it were equally
well implemented.  Or it might not.  You'd have to invest a lot
of work to find out, and might well discover that your work
was wasted.

            regards, tom lane