Re: Unnecessary function calls - Mailing list pgsql-general

From Markus Schiltknecht
Subject Re: Unnecessary function calls
Date
Msg-id 1146572198.18433.54.camel@fotomarburg
Whole thread Raw
In response to Unnecessary function calls  (Markus Schiltknecht <markus@bluegap.ch>)
Responses Re: Unnecessary function calls
List pgsql-general
Hello Terry,

Thanks a lot. That's so simple I didn't see it. (The original query is
much more complex.)

The only problem is, rank is not a column of category itself, but a
joined row. With this solution, the join will have to be performed
twice. But since this doesn't cost that much and because the second join
is only done for 5 rows at the max this does not hurt.

The more complete query now looks a little ugly:

SELECT id, get_category_text_path(id), r.rank
FROM category
JOIN rank_lookup AS r ON cat_id = id
WHERE id IN (
    SELECT c.id
    FROM category AS c
    JOIN rank_lookup AS rr ON rr.cat_id = c.id
    ORDER BY rr.rank
    LIMIT 5
)

It's not possible to optimize out that second join, is it?

Regards

Markus

On Tue, 2006-05-02 at 07:39 -0400, Terry Fielder wrote:
> SELECT id, get_category_text_path(id)
> FROM category
> WHERE id IN (
>    SELECT c.id
>    FROM category AS c
>    ORDER BY c.rank
>    LIMIT 5
> )



pgsql-general by date:

Previous
From: Martijn van Oosterhout
Date:
Subject: Re: Unnecessary function calls
Next
From: Terry Fielder
Date:
Subject: Re: Unnecessary function calls