Problem with indices (?) in PostgreSQL 7.0.2

From: " " <jem(at)postdep(dot)yaroslavl(dot)su>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Problem with indices (?) in PostgreSQL 7.0.2
Date: 2001-10-02 06:06:27
Message-ID: 006b01c14b08$5f85a460$3301a8c0@domain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello!

I've got this weird problem in PostgreSQL 7.0.2 running on FreeBSD
3.1-RELEASE.

There's a table 'cat':

index | char(5) |
mainindex | char(5) |
discount | integer |
type | integer |
t1 | integer |
t2 | integer |
t3 | integer |
mintime | integer |
numtime | integer |
timetype | integer |
name | char(254) |
info | text |
pub | integer |
price | float4 |
uname | text |

It has some indices:
i_index,
i_mainindex,
i_name,
i_t1,
i_t2,
i_t3

Okay, I ran 'psql -U sub sub' (table 'cat' is in 'sub' database) and
typed in this command:
select count(*) from cat where name like 'A%';
(I'd like get count of records whose names starts with capital 'A'
letter)
It works about two seconds and prints, say, 15. Okay, this time
looks reasonable on this old P-120/32.
With other letters ('B', 'C', ..., 'Y') this commands works fine
too. BUT:
select count(*) from cat where name like 'Z%'; works 5.30
minutes (330 seconds!!!!!) and prints 2 (well, that's correct value but very
strange time).

There was no other background processes for 'Z'-case other than for
'A'...'Y' cases. I tryed this many times without any success for 'Z'.

I don't know whether this is my problem or PGSQL's one. I've got an
index for this field ('i_name' on field 'name'), and 'EXPLAIN' says that
it'll use index scan
in all cases (for all letters)

Any suggestions?

P.S. "select * from cat where name like 'A%'" works fine for 'A'...'Y' but
'Z'.

----------
With best regards, Eugene Trofimov
Yaroslavl Postal Service, Russia
jem(at)postdep(dot)yaroslavl(dot)su // (0852) 47-30-75 // ICQ 122321282

Browse pgsql-general by date

  From Date Subject
Next Message Thirumoorthy Bhuvneswari 2001-10-02 13:09:26 Using Postgresql-7.0.1 in multiple clients
Previous Message Tatsuo Ishii 2001-10-02 05:47:59 Re: [GENERAL] Problem with the accents