ORDER BY ... LIMIT.. performance
От | john cartmell |
---|---|
Тема | ORDER BY ... LIMIT.. performance |
Дата | |
Msg-id | 94B61ED0D8770A4A98A3DBD72DBBA1F821A80A@mediaexch01.mediaburst.co.uk обсуждение исходный текст |
Ответы |
Re: ORDER BY ... LIMIT.. performance
Re: ORDER BY ... LIMIT.. performance |
Список | 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
В списке pgsql-performance по дате отправления: