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

From: jkoceniak(at)mediamath(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14399: Order by id DESC causing bad query plan
Date: 2016-10-28 00:16:36
Message-ID: 20161028001636.15988.55966@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message francois.atilio 2016-10-28 04:59:09 BUG #14400: pgAdmin 4 error 'utf8' codec can't decode byte...
Previous Message Atilio Francois 2016-10-27 18:50:11 pgAdmin 4 error 'utf8' codec can't decode byte...