Thread: Need help on index!!!

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.


Thanks a lot.


Johnson

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.

Try single quotes around 10000:

select * from pmmeasure where dataid = '10000';

This is understood to mean:

select * from pmmeasure where dataid = '10000'::bigint;

Without the quotes this is understood to be

select * from pmmeasure where dataid = ('10000'::int4)::bigint;

which is not what you want anyway.


Dan Weston


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.
>
>
> Thanks a lot.
>
>
> Johnson
>
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

Please try
VACUUM ANALYZE pmmeasure;

--
Saito Yasuhiro
yassaito@jcom.home.ne.jp