RES: [SQL] Queries not using Index

Hi!

What kind of index is t2002_06_station_idx?
Have you done this SELECT command below, right?

select * from t2002_06 WHERE station = 'SAMI4';

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').

> -----Mensagem original-----
> De: Daryl Herzmann [mailto:akrherz(at)iastate(dot)edu]
> Enviada em: terça-feira, 23 de julho de 2002 22:57
> Para: Christopher Kings-Lynne
> Cc: Stephan Szabo; pgsql-sql(at)postgresql(dot)org
> Assunto: Re: [SQL] Queries not using Index
>
>
> Hi,
>
> >You _have_ actually run ANALYZE on the table, right?
>
> 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=35169
> width=47) (actual
> time=20.51..1717.78 rows=38146 loops=1)
> Total runtime: 1730.63 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_idx on t2002_06
> (cost=0.00..132773.85
> rows=35169 width=47) (actual time=74.86..299.53 rows=38146 loops=1)
> Total runtime: 313.42 msec
>
> EXPLAIN
>
> Any thoughts? I am sorry to be causing all this trouble. I
> just want my
> queries to voom-voom!! Interestingly enough, I see that the
> SEQ SCAN is
> now estimated at 1730.63, when I first posted to this list,
> it was 3900.00
> or so. Errrr
>
> Thanks,
> Daryl
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org
>