ORDER BY time consuming

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

ORDER BY time consuming

Ben-Nes Yonatan
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?

Thanks in advance,
Ben-Nes Yonatan
Canaan Surfing ltd.
http://www.canaan.net.il

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: ORDER BY time consuming

Jim C. Nasby
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      [hidden email]
Pervasive Software        http://pervasive.com        512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: ORDER BY time consuming

Thomas F. O'Connell
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      [hidden email]
> Pervasive Software        http://pervasive.com        512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match