On Fri, 21 Jun 2002, Johnson Ma wrote:
> Hi guru
>
> I have a table with 300,000 records. I also created a index on it. But
> the postgresql use sequence scan every time, when i search one record.
> It is so slow. why?
>
> here is create table script
> create table pmmeasure (dataid bigint not null,
> measurenum bigint DEFAULT nextval('measurenum')
> UNIQUE NOT NULL,
> name varchar(100) not null,
> value varchar(100) not null,
> constraint pk_pmmeasure primary key (measurenum),
> constraint fk_pmmeasure_1 foreign key (dataid)
> references pmdata (dataid)
> );
> create index index_pm on pmmeasure (dataid);
>
>
> When I do like
> explain analyze select * from pmmeasure where dataid = 10000;
>
> it shows that postgresql always sequence scan for that record.
There's a known problem with bigint that you need to either quote
the constant or explicitly cast it to bigint in order to get an
index scan. There's more info in the archives if you're interested.