ORDER BY ... LIMIT.. performance

From: "john cartmell" <john(dot)cartmell(at)mediaburst(dot)co(dot)uk>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: ORDER BY ... LIMIT.. performance
Date: 2002-12-05 09:51:09
Message-ID: 94B61ED0D8770A4A98A3DBD72DBBA1F821A80A@mediaexch01.mediaburst.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jochem van Dieten 2002-12-05 10:01:15 Re: Is a better way to have the same result of this query?
Previous Message Ron Johnson 2002-12-05 05:26:48 Re: Is a better way to have the same result of this