RE: primary key scans in sequence

actually the serials are declared int4 (integer),
I tried to use your work around anyway, but it didn't work...:

dsc_competition=# \d classes
Table "classes"
Attribute | Type | Modifier

------------+---------+-------------------------------------------------
-
id | integer | not null default nextval('classes_id_seq'::text)
name | text | not null
definition | text |
active | boolean | default 't'::bool
Indices: classes_pkey,
index_classes_name,
unq_classes_name

dsc_competition=# explain select * from classes where id = int8(4);
NOTICE: QUERY PLAN:

Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)

EXPLAIN
dsc_competition=# explain select * from classes where id = int4(4);
NOTICE: QUERY PLAN:

Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)

EXPLAIN
dsc_competition=# explain select * from classes where id = int2(4);
NOTICE: QUERY PLAN:

Seq Scan on classes (cost=0.00..1.07 rows=1 width=29)

EXPLAIN

Thing I descovered after i posted to the group was that after creating
the scheme again, the indexes are used! after vacuum (analyze) the use
of indexes was gone again on certain tables...

Any other suggestions?

-----Original Message-----
From: bernd pinter [mailto:bernd(at)uptime(dot)at]
Sent: donderdag 31 mei 2001 8:53
To: Koen Antonissen
Subject: Re: [SQL] primary key scans in sequence

the problem is the optimizer.
you use a int8 as primary key.
so if you do somthing like
"select * from sponsors where id = 34;"
then the optimizer interprets the 34 as an int4 => id is int8, so
postgres cant use the primary
key-index.

the workaround is simple. say that you are lookiong for an int8 (instead
of an int4):

"select * from sponsors where id = int8(34);"
thats it! now you convert 34 to an int8, postgres can use the
index......

bernd.

Koen Antonissen wrote:

> I have the same problem, my primary key is defined as a serial though.
> Other tables use tables are defined as serials as well, but DO use
Index
> Scans
> some tables do, some tables don't, even when creating 'my own' index
on
> the primary key, it still uses sequencial scans!
>
>
> This one works fine:
> Table "sponsors"
> Attribute | Type | Modifier
>
>
-----------+---------+--------------------------------------------------
> ---
> id | integer | not null default
nextval('sponsors_id_seq'::text)
> name | text | not null
> email | text |
> logo | text | not null default
> 'images/sponsors/logo_default.gif'
> url | text |
> qoute | text |
> active | boolean | default 't'::bool
> main | boolean | default 'f'::bool
> Indices: index_sponsors_main,
> index_sponsors_name,
> sponsors_pkey,
> unq_sponosrs_name
>
> dsc_competition=# explain select * from sponsors where id = 4;
> NOTICE: QUERY PLAN:
>
> Index Scan using sponsors_pkey on sponsors (cost=0.00..2.01 rows=1
> width=66)
>
> EXPLAIN
>
> Now this one doesn't:
> Table "teams"
> Attribute | Type | Modifier

>
-----------+---------+--------------------------------------------------
> id | integer | not null default nextval('teams_id_seq'::text)
> name | text | not null
> mgr_name | text |
> address | text |
> zipcode | text |
> city | text |
> country | text |
> email | text |
> telnr | text |
> mobnr | text |
> faxnr | text |
> logo | text | not null default
'images/teams/logo_default.gif'
> movie | text |
> url | text |
> qoute | text |
> active | boolean | default 't'::bool
> Indices: index_teams_id, <=!!! 'my own' index
> index_teams_name,
> teams_pkey, <=normal pkey index
> unq_teams_name
>
> NOTICE: QUERY PLAN:
>
> Seq Scan on teams (cost=0.00..1.09 rows=1 width=173)
>
> EXPLAIN
>
>
> I really don't understand the difference between the two, and it
didn't
> work before i created an extra index on id...
>
> Kind regards,
> Koen Antonissen
>
>
> -----Original Message-----
> From: Richard Poole [mailto:richard(dot)poole(at)vi(dot)net]
> Sent: vrijdag 30 maart 2001 18:12
> To: bernd
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] primary key scans in sequence
>
>
> 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
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo(at)postgresql(dot)org