I need help finding out why the following query that aggregates peoples' responses by "item" slows down considerably after the data being aggregated reaches a certain size.
select t1.item, count(t1.val) as count, round(avg(t1.resp), 2) as mean, round(std(t1.resp), 2) as std from person_response as t1 join person_info as t2 on t1.person_id = t2.id where t1.resp between 1 and 5 and t2.is_valid = 1 and (t2.attr1, t2.attr2) in ((?, ?), ...) and t2.attr3 in (?, ...) and t1.item in (?, ...) group by t1.item ;
t1 has about 15 million rows and t2 has about 200,000 rows. All columns involved in the query are indexed. If the WHERE clause captures under 50,000 rows, execution is generally under 100 ms. If the WHERE clause captures 138,665 rows, execution is under 500 ms. But when I extend the WHERE clause to capture 150,685 rows, execution takes considerably longer at 3 m 40 s 366 ms. Nearly 4 minutes!