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

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