Thread: Problem with indices (?) in PostgreSQL 7.0.2

        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@postdep.yaroslavl.su // (0852) 47-30-75 // ICQ 122321282