From: | "bernd" <bernd(at)matrixware(dot)at> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | primary key scans in sequence |
Date: | 2001-03-29 13:47:58 |
Message-ID: | 99ve9e$gie$1@news.tht.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
hey i have the following table def (834.000 rows, vaccum analyze'd):
dl_online=# \d mitglied
Table "mitglied"
Attribute | Type | Modifier
----------------+--------------+----------------------------
mitgliedid | bigint | not null
dlnummer | varchar(30) |
vorname | varchar(50) |
zuname | varchar(50) | not null
geburtsdatum | varchar(20) |
aktiv | boolean | not null default 't'::bool
strasse | varchar(255) |
plz | varchar(25) |
ort | varchar(255) |
telefon | varchar(255) |
eintrittsdatum | varchar(20) |
geschlechtid | bigint | not null default 3
treuelevelid | bigint | not null default 1
clubmitglied | boolean | not null default 'f'::bool
bemerkungen | text |
mid | bigint |
Indices: mitglied_dlnummer_idx, [on dlnummer]
mitglied_pkey [on mitgliedid]
--------------------
ok; i use 2 querys:
1) get one row over dlnummer:
dl_online=# explain select * from mitglied where dlnummer = '098765432';
NOTICE: QUERY PLAN:
Index Scan using mitglied_dlnummer_idx on mitglied (cost=0.00..4.77 rows=1
width=154)
EXPLAIN
2) get one row over the primatry key (mitgliedid):
dl_online=# explain select * from mitglied where mitgliedid = 833228;
NOTICE: QUERY PLAN:
Seq Scan on mitglied (cost=0.00..18674.74 rows=1 width=154)
EXPLAIN
why doesn't use postrges in (2) the primary-key-index?? take a look at the
cost! and both queries returns only ONE row (the optimizer knows that fact).
and the worst. in(2) the query take ~3sec. in this time the cpu works on
99.9% (rh-linux 7 on a compac dc10 -alpha).
the other works fine (no time to calculate, "no" use of the cpu!).
PS: i tried also "set ENABLE_SEQSCAN to OFF". no chance, (2) will work with
Seq Scan.
thx to ANY hint!!
bernd.
From | Date | Subject | |
---|---|---|---|
Next Message | Mathijs Brands | 2001-03-29 14:05:54 | Re: Calling Java from psql (was Re: requesting help) |
Previous Message | A James Lewis | 2001-03-29 13:46:03 | Re: Calling Java from psql (was Re: requesting help) |