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