I have a Linux Mandrake 8.0 server and I have installed also 7.1.2 and
7.1.3 versions from RPM's compiled for Mandrake 8.0
The 7.1.3 version was from
ftp://postgresql.lcs.mit.edu/pub/PostgreSQL/v7.1.3/
and 7.1.2 and 7.1.3 have the same bug:
When retrieving data from tables based on a WHERE clause with regexp on
an indexed btree column as follows :
select * from mytable where column ~ '^pattern'
the index is not used!!!
I know that if there is a btree index and the regexp has the start of
the values in that column, the index is used.
I was using that in some of my programs.
When moving to Mandrake 8.0 and installed those RPM's , the feature
dissapeared.
I was downloading the 7.1.3 tar.gz sources, compile them on the same
machine, installed them in another directory, rebuild the database,
vacuum analyze it and check again. Now it works:
============================================================================
contabil=# explain select * from conturi where id ~ '^4:';
NOTICE: QUERY PLAN:
Index Scan using conturi_id on conturi (cost=0.00..32.66 rows=44
width=66)
NOTICE: QUERY PLAN:
Index Scan using conturi_id on conturi (cost=0.00..32.66 rows=44
width=66)
EXPLAIN
contabil=#
=============================================================================
when I tried to use the same database with the new binaries compiled by
myself, it refused :
[postgres@teo pgsql]$ FATAL 2: database was initialized with LC_COLLATE
'en_US' and LC_CTYPE 'en_US', but the backend was compiled without locale support. looks like you need to
initdbor recompile.
So I installed my compiled binaries in another place (/usr/local/pgsql)
create a new database and restore the database from a previously
pg_dumped image.
On both 7.1.2 and 7.1.3 binery packages for Mandrake I was using the
same database, the same "vacuum analyze" before trying the explain, the
same results: "Sequential scan" , it didn't wanted to use the index.
any clues?
Was it something related with LC_COLLATE or LC_CTYPE?
Constantin Teodorescu
Braila, ROMANIA