Re: ORDER BY time consuming

You're also free to set sort_mem (7.4.x) or work_mem (8.0.x) on a per
session basis, so you could try experimenting with raising the value
of those settings during sessions in which your query is running.

--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC

Strategic Open Source: Open Your i™

http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)

On Aug 21, 2005, at 12:01 PM, Jim C. Nasby wrote:

> On Sun, Aug 21, 2005 at 12:04:01PM +0200, Ben-Nes Yonatan wrote:
>
>> Hi All,
>>
>> I got a table with about 4.5 millions rows in it which is
>> connected to
>> another table with about 60 millions rows which are used as
>> keywords for
>> searching.
>>
>> I succeded to create fast queries on the first table that finds a
>> row at
>> the first table which is connected to up to 4 diffrent keywords at
>> the
>> second table and LIMIT the result to 12 (I want to allow the
>> surfers of
>> the site to press back and next to see more products so ill make
>> it with
>> OFFSET).
>>
>> I want to be able to order my result by a specific column but when I
>> insert ORDER BY into the query (and any other query that I tried) it
>> becomes extremly slow, what can I do to solve this problem?
>>
>
> Your question is too generic to answer specifically, but I suspect
> that
> if you use your un-ordered query as a subquery in the FROM clause and
> then order that it will work well. IE:
>
> SELECT *
>     FROM (SELECT ...) a
>     ORDER BY f1, f2, f3
> --
> Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
> Pervasive Software        http://pervasive.com        512-569-9461