I am not sure whether this is a know problem but we discovered this the
other day.
We are using PostgreSQL 7.2.1 on Redhat 7.3.
The table has about over a million rows (~1.4).
The query concerned is of the form
SELECT *
FROM tblCompany
WHERE lower(companyname) like 'company%'
ORDER BY companyname
LIMIT 20,0
There is a functional index lower(companyname) for the like clause.
Without the LIMIT clause the query takes approximately 3-5 seconds to
return.
If total number of rows returned without the LIMIT clause is greater
than 20 records, then the above query also takes th same amount of time.
But if the the total number of rows is 20 or less then the time taken
for the above query to return goes up to 20-30 seconds. Has anyone else
come across this. We have managed to get round it by performing a count
first and only performing the LIMIT if there are enough rows but surely
the query should be able to do this itself!
John Cartmell