Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f2UESEK51809 for ; Fri, 30 Mar 2001 09:28:14 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f2TEAHK23194 for ; Thu, 29 Mar 2001 09:10:17 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f2TDjLr16977 for pgsql-sql@postgresql.org; Thu, 29 Mar 2001 08:45:21 -0500 (EST) (envelope-from news) From: "bernd" X-Newsgroups: comp.databases.postgresql.sql Subject: primary key scans in sequence Date: Thu, 29 Mar 2001 15:47:58 +0200 Organization: www.matrixware.at Lines: 54 Message-ID: <99ve9e$gie$1@news.tht.net> X-Complaints-To: scrappy@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 To: pgsql-sql@postgresql.org X-Archive-Number: 200103/638 X-Sequence-Number: 2314 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.