Re: BUG #14302: SQL with LIMIT degrades performance seriously

Yes, I try it and it works now! Thank you very much, Francisco Olarte,
and Andrew Gierth!

On Thu, Sep 1, 2016 at 1:41 AM, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:

> >>>>> "Kaijiang" == Kaijiang Chen <chenkaijiang(at)gmail(dot)com> writes:
>
> Kaijiang> It couldn't solve the problem.
> Kaijiang> I've already created 2 btree indexes, one for parent_id, the
> Kaijiang> other for user_id. Do you mean to create an multi-column
> Kaijiang> index on (parent_id, user_id)?
>
> Yes. The 2 separate indexes are not sufficient, but you can omit the
> index on parent_id alone if you create the multi-column index.
>
> Kaijiang> still couldn't solve the problem, since we still need index
> Kaijiang> for user_id (for other sql) and planner will turn to user_id
> Kaijiang> index.
>
> The planner should not do that (if it does, it's a bug).
>
> The plan you're looking for is:
>
> Limit
> -> MergeAppend
> -> Index scan on parent_id_user_id_idx
> Index Cond: (parent_id = ?)
> -> Index scan on parent_id_user_id_idx
> Index Cond: (parent_id = ?)
> ...
>
> Note the use of Index Cond rather than Filter, this is important.
>
> --
> Andrew (irc:RhodiumToad)
>