Re: Interesting query plan change linked to the LIMIT parameter

Thanks for the rapid response.

I can understand the way the planner makes its guess, but as a matter of fact, he'll be nearly always wrong, just becausethe most commented articles have only around 5000 or so comments.  I ran the explain  analyze tonight and got this results :

EXPLAIN ANALYZE SELECT _comment.id,
        (get_comment_response(_comment.id)).id AS r_id
FROM   _comment
INNER JOIN _article
        ON _article.id = _comment.parent_id
WHERE  _comment.parent_id = '17355952'
ORDER BY _comment.id ASC
OFFSET 0
LIMIT 1;
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..3588.42 rows=1 width=8) (actual time=498597.115..498597.116 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..28524312.40 rows=7949 width=8) (actual time=498597.114..498597.114 rows=1 loops=1)
         ->  Index Scan using _comment_pkey on _comment  (cost=0.00..28448324.73 rows=7949 width=16) (actual time=498473.360..498473.360 rows=1 loops=1)
               Filter: (parent_id = 17355952::bigint)
         ->  Index Scan using _article_pkey on _article  (cost=0.00..9.55 rows=1 width=8) (actual time=63.465..63.465 rows=1 loops=1)
               Index Cond: (_article.id = 17355952::bigint)
 Total runtime: 498615.230 ms
(7 rows)

EXPLAIN ANALYZE SELECT _comment.id,
        (get_comment_response(_comment.id)).id AS r_id
FROM   _comment
INNER JOIN _article
        ON _article.id = _comment.parent_id
WHERE  _comment.parent_id = '17355952'
ORDER BY _comment.id ASC
OFFSET 0
LIMIT 1000;
                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10261.19..10263.69 rows=1000 width=8) (actual time=127.037..127.267 rows=1000 loops=1)
   ->  Sort  (cost=10261.19..10281.06 rows=7949 width=8) (actual time=127.036..127.128 rows=1000 loops=1)
         Sort Key: _comment.id
         Sort Method:  top-N heapsort  Memory: 95kB
         ->  Nested Loop  (cost=0.00..9825.35 rows=7949 width=8) (actual time=0.472..122.986 rows=4674 loops=1)
               ->  Index Scan using _article_pkey on _article  (cost=0.00..9.55 rows=1 width=8) (actual time=0.011..0.013 rows=1 loops=1)
                     Index Cond: (id = 17355952::bigint)
               ->  Index Scan using idx_comment_parent_id on _comment  (cost=0.00..9716.44 rows=7949 width=16) (actual time=0.235..32.869 rows=4674 loops=1)
                     Index Cond: (_comment.parent_id = 17355952::bigint)
 Total runtime: 127.410 ms
(10 rows)

As you can see, the time is dramaticaly longuer with the LIMIT 1 (or in our case, LIMIT 2).

Yannick.