Re: BUG #14399: Order by id DESC causing bad query plan

Hi I was wondering if this could get approved by the moderator.

One added note: if I actually drop the index then the query plan does a top-N heap sort instead of nested join filter and runs in 28ms.

Sort Method: top-N heapsort Memory: 94kB

Thanks!

On 10/27/16, 5:16 PM, "jkoceniak(at)mediamath(dot)com" <jkoceniak(at)mediamath(dot)com> wrote:

>The following bug has been logged on the website:
>
>Bug reference: 14399
>Logged by: Jamie Koceniak
>Email address: jkoceniak(at)mediamath(dot)com
>PostgreSQL version: 9.4.6
>Operating system: Linux
>Description:
>
>One table has 2M records (orders) joining to another table with 75K records
>(customers).
>
>Query:
>select * FROM
> orders t1
> JOIN customer t2 ON (t1.customer_id = t2.id)
> WHERE
> t2.id IN (select distinct customer_id from valid_customers)
> ORDER BY t1.id
> LIMIT 10 ;
>
>-- valid customers subquery contains 200 records.
>
>For some reason the nested join is doing filter:
> Rows Removed by Join Filter: 410976415
>
>See anonymized query plan here:
>https://explain.depesz.com/s/k9s5
>
>If I removed order by, query returns in 1.5ms
>