pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRES: [SQL] Queries not using Index
4+ messages / 4 participants
[nested] [flat]
* RES: [SQL] Queries not using Index
@ 2002-07-24 14:41 Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
0 siblings, 2 replies; 4+ messages in thread
From: Elielson Fontanezi @ 2002-07-24 14:41 UTC (permalink / raw)
To: Daryl Herzmann <akrherz@iastate.edu>; +Cc: pgsql-sql; pgsql-general <pgsql-general@postgresql.org>
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: RES: [SQL] Queries not using Index
@ 2002-07-24 15:18 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
1 sibling, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2002-07-24 15:18 UTC (permalink / raw)
To: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>; +Cc: Daryl Herzmann <akrherz@iastate.edu>; pgsql-sql; pgsql-general <pgsql-general@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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: RES: [SQL] Queries not using Index
@ 2002-07-24 15:45 Daryl Herzmann <akrherz@iastate.edu>
parent: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
1 sibling, 1 reply; 4+ messages in thread
From: Daryl Herzmann @ 2002-07-24 15:45 UTC (permalink / raw)
To: Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>; +Cc: pgsql-sql; pgsql-general <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: RES: [SQL] Queries not using Index
@ 2002-07-24 17:12 Phil Davey <pd213@mole.bio.cam.ac.uk>
parent: Daryl Herzmann <akrherz@iastate.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Phil Davey @ 2002-07-24 17:12 UTC (permalink / raw)
To: pgsql-sql; +Cc: pgsql-general <pgsql-general@postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-07-24 17:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-24 14:41 RES: [SQL] Queries not using Index Elielson Fontanezi <ElielsonF@prodam.sp.gov.br>
2002-07-24 15:18 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-24 15:45 ` Daryl Herzmann <akrherz@iastate.edu>
2002-07-24 17:12 ` Phil Davey <pd213@mole.bio.cam.ac.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox