query optimiser changes 6.5->7.0 - Mailing list pgsql-general

Hi,

I apologise if I am being stupid or this is the wrong list, but here goes
anyway ;-)

I have a table of the following form:

           Table "gazet"
 Attribute |    Type     | Modifier
-----------+-------------+----------
 country   | integer     |
 name      | varchar(30) |
 lat       | float4      |
 long      | float4      |
 score     | integer     |
Index: gazet_index

gazet_index is an index defined as

create index gazet_index on gazet (country)

This table has a couple of million rows and I am executing the query:-

select * from gazet where country=1 and lower(name) = 'placename';

I have been running this with no problems on Postgres 6.5.1 and this query
takes about a second. I have now set-up a dedicated Postgres server using
version 7.0 and exported the entire database into it using a pg_dump.  I
have then run vacuum to recreate indexes etc, but the query takes 7-8
seconds now.  I have run explain on the query and it shows that it is just
performed a sequential scan on version 7.0

Seq Scan on gazet  (cost.....)

On the old version (6.5.1) it reports

Index Scan using gazet_index on gazet  (cost=....

Any suggestions as to how I can improve performance on this databases new
server?

Many thanks

Simon


_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
_/         Simon Hardingham - netXtra Ltd - UK          _/
_/ Tel: +44 (0) 1787 319393    Fax: +44 (0) 1787 319394 _/
_/ http://www.netxtra.co.uk         simon@netxtra.co.uk _/
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/