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

>>>>> "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)