Re: ORDER BY ... LIMIT.. performance

John,

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

First of all, there are a few bug-fixes between 7.2.1 and 7.2.3. One
relates to backups, and another to security. So you should upgrade to
7.2.3 immediately -- no init or restore from backup required (not
version 7.3, which has some significant changes).

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

This seems very odd. Please do the following:

1) Post an EXPLAIN ANALYZE statement for the above query, with limit,
that returns in 3-5 seconds.
2) Post an EXPLAIN ANALYZE for a query that returns slowly (20-30
seconds).

Thanks!

-Josh