pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Table scan instead of index scan
2+ messages / 2 participants
[nested] [flat]

* Table scan instead of index scan
@ 2001-07-24 13:07  Mário Sérgio G Antunes <mariosergioa@msantunes.com.br>
  0 siblings, 1 reply; 2+ messages in thread

From: Mário Sérgio G Antunes @ 2001-07-24 13:07 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Table scan instead of index scan
@ 2001-07-24 13:34  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Mário Sérgio G Antunes <mariosergioa@msantunes.com.br>
  0 siblings, 0 replies; 2+ messages in thread

From: Stephan Szabo @ 2001-07-24 13:34 UTC (permalink / raw)
  To: Mário Sérgio G Antunes <mariosergioa@msantunes.com.br>; +Cc: pgsql-sql

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)




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-07-24 13:34 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-24 13:07 Table scan instead of index scan Mário Sérgio G Antunes <mariosergioa@msantunes.com.br>
2001-07-24 13:34 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox