Обсуждение: 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@iastate.edu]
> Enviada em: terça-feira, 23 de julho de 2002 22:57
> Para: Christopher Kings-Lynne
> Cc: Stephan Szabo; pgsql-sql@postgresql.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@postgresql.org
>

On Wed, 24 Jul 2002, Elielson Fontanezi wrote:

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


Btree indexes are happy being used for equality searches
in PostgreSQL as long as the optimizer thinks it's worth
it. Hash indexes are currently pretty broken, it's better
to just pretend they aren't there.

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

In Postgres, the hash index is slow and inefficient (it's a bit better
in7.3), and I believe btree is generally recommended over hash even for '='
instances.

Chris

On Wed, 24 Jul 2002, Daryl Herzmann wrote:
[lots of chopping and rearranging...]
> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
> Seq Scan on t2002_06  (cost=0.00..35379.69)
> Total runtime: 2452.14 msec
>
> snet=# set enable_seqscan=off;
> snet=# explain analyze select * from t2002_06 WHERE station = 'SAMI4';
> Index Scan using t2002_06_station_hash_idx on t2002_06
> (cost=0.00..132190.93)
> Total runtime: 325.22 msec

I don't know how these indexes actually work, but just looking at the
numbers here, it uses a seq scan because it thinks a seq scan costs far
less than an index scan (35379 v 132190) even though the actual runtime is
much less for the index scan (2452 msec v 325 msec).

*why* it's guessing wrong, I haven't got a clue. =)

--
Phil Davey
Computer Officer
Hughes Hall College, Cambridge
Email phil.davey@hughes.cam.ac.uk

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