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
