Re: multiple joins + Order by + LIMIT query performance issue

Thanks a lot for your answer, there are some points I didnt understand

On May 6, 2008, at 6:43 PM, Shaun Thomas wrote:

>
> The second query says "Awesome! Only one network... I can just search
> the index of t_event backwards for this small result set!"
>

Shouldnt It be the opposite? considering that only a few row must be
"joined" (Sorry but I'm not familiar with DBMS terms) with the
t_event table, why not simply look up the corresponding rows in the
t_event table using the service_id foreign key, then do the sort? Isnt
the planner fooled by the index on the sorting column? If I remove the
index the query runs OK.

> But here's the rub... try your query *without* the limit clause, and
> you
> may find it's actually faster, because the planner suddenly thinks it
> will have to scan the whole table, so it choses an alternate plan
> (probably back to the nest-loop). Alternatively, take off the order-
> by
> clause, and it'll remove the slow backwards index-scan.

You are right, if i remove the order-by clause It doesnt backwards
index-scan.

And if I remove the limit and keep the order-by clause, the backwards
index-scan is gone too, and the query runs in a few millisecs!!

This is crazy, so simply by adding a LIMIT to a query, the planning is
changed in a very bad way. Does the planner use the LIMIT as a sort of
hint?

Thank you for your explanations,

Antoine Baudoux