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
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 |