From: Richard Poole <richard.poole@vi.net>
To: bernd <bernd@matrixware.at>
Cc: pgsql-sql@postgresql.org
Subject: Re: primary key scans in sequence
Date: Fri, 30 Mar 2001 17:11:47 +0100
Message-ID: <20010330171147.A8347@office.vi.net> (raw)
In-Reply-To: <99ve9e$gie$1@news.tht.net>
References: <99ve9e$gie$1@news.tht.net>
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
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: richard.poole@vi.net, bernd@matrixware.at
Subject: Re: primary key scans in sequence
In-Reply-To: <20010330171147.A8347@office.vi.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