primary key scans in sequence

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.

Responses

Browse pgsql-sql by date

  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)