pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: bernd <bernd@matrixware.at>
To: pgsql-sql@postgresql.org
Subject: primary key scans in sequence
Date: Thu, 29 Mar 2001 15:47:58 +0200
Message-ID: <99ve9e$gie$1@news.tht.net> (raw)

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.





view thread (8+ messages)  latest in thread

Message-ID: <99ve9e$gie$1@news.tht.net>
Permalink:  ../99ve9e$gie$1@news.tht.net/
Also on:    postgresql.org/message-id/99ve9e$gie$1@news.tht.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: bernd@matrixware.at
  Subject: Re: primary key scans in sequence
  In-Reply-To: <99ve9e$gie$1@news.tht.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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