Re: primary key scans in sequence

On Thu, Mar 29, 2001 at 03:47:58PM +0200, bernd wrote:
> 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).

Because the type of the "mitgliedid" is "bigint", but the type of the
constant "833228" is "integer" (I think; certainly it isn't "bigint").
Postgres doesn't realise that it can use an index on a bigint to do
comparisons to an integer. If you explicitly cast the constant to a
bigint, it should be willing to do an index scan, like so:

select * from mitglied where mitgliedid = 833228::bigint

Yes, this is a bit unpleasant to have to in your client code, and no,
I don't know if there's a neater way to let Postgres know it can use
this index for this query. But what I've just described does work.

Richard