primary key scans in sequence
От | bernd |
---|---|
Тема | primary key scans in sequence |
Дата | |
Msg-id | 99ve9e$gie$1@news.tht.net обсуждение исходный текст |
Ответы |
Re: primary key scans in sequence
Re: primary key scans in sequence |
Список | 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 nulldlnummer | varchar(30) |vorname | varchar(50) |zuname | varchar(50) | not nullgeburtsdatum | varchar(20) |aktiv | boolean | not null default 't'::boolstrasse | varchar(255) |plz | varchar(25) |ort | varchar(255) |telefon | varchar(255) |eintrittsdatum | varchar(20) |geschlechtid | bigint | not null default 3treuelevelid | bigint | not null default 1clubmitglied | boolean | not null default'f'::boolbemerkungen | 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:IndexScan 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.
В списке pgsql-sql по дате отправления: