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@tao11.riddles.org.uk>
wrote:

> >>>>> "Kaijiang" == Kaijiang Chen <chenkaijiang@gmail.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)
>