RE: primary key scans in sequence - Mailing list pgsql-sql

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 nullemail     |
text   | logo      | text    | not null default
 
'images/sponsors/logo_default.gif'url       | text    | qoute     | text    | active    | boolean | default
't'::boolmain     | 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 nullmgr_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.poole@vi.net]
Sent: vrijdag 30 maart 2001 18:12
To: bernd
Cc: pgsql-sql@postgresql.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@postgresql.org