[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-bugs
Subject:    Re: [BUGS] BUG #14302: SQL with LIMIT degrades performance seriously
From:       Andrew Gierth <andrew () tao11 ! riddles ! org ! uk>
Date:       2016-08-31 17:41:06
Message-ID: 87inug4zvx.fsf () news-spur ! riddles ! org ! uk
[Download RAW message or body]

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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[prev in list] [next in list] [prev in thread] [next in thread]