RES: RES: [SQL] Queries not using Index

-----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