Re: primary key scans in sequence

bernd writes:

> 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]
> --------------------

> 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

This is because the system is not smart enough to match up a bigint =
integer comparison with an index scan on a bigint column. Quoting the
number, '833228', should fool it sufficiently to make this work.
Incidentally, it seems unlikely that you need to use bigint for membership
ids, unless you plan on more than 2*10^9 members.

--
Peter Eisentraut peter_e(at)gmx(dot)net http://yi.org/peter-e/