Обсуждение: Table scan instead of index scan

Hi,

I am sure PostgreSQL is a very powerful product!


I use PostgreSQL 7.1.2.4 in two servers under RedHat 7.0.
Our database has a table like that:
create table tb_client
(  id_client varchar(32),  client_name varchar(40),  bdate date, ...
);

create index xiftb_client on tb_client(id_client);

When I execute the following query:

SELECT * FROM tb_cliente WHERE id_client LIKE '1234%';

One server executes index search and other full table scan.

I don't know the reason.

If you could help me I would appreciate a lot.

Thanks,

Mário Sérgio

On Tue, 24 Jul 2001, [ISO-8859-1] M�rio S�rgio G Antunes wrote:

> I use PostgreSQL 7.1.2.4 in two servers under RedHat 7.0.
> Our database has a table like that:
> create table tb_client
> (
>    id_client varchar(32),
>    client_name varchar(40),
>    bdate date,
>   ...
> );
> 
> create index xiftb_client on tb_client(id_client);
> 
> When I execute the following query:
> 
> SELECT * FROM tb_cliente WHERE id_client LIKE '1234%';
> 
> One server executes index search and other full table scan.
> 
> I don't know the reason.
> 
> If you could help me I would appreciate a lot.

There are a few possibilities.  Are both servers vacuum analyzed?
Are both compiled the same way?  Are both running in the same locale
and what is it (which affects like optimization)?  What is explain's row
estimates on the two machines (if one is much larger percentage of the
table than the other, that'd also force a table scan)