I have a query that looks like
select count(*) FROM (joins) WHERE customer_id in (select id from customer limit 117) AND ... (more statements)
It involves tables with several hundred million records, table sizes around 60GB including all the indexes. The result for 117 would be a count of 1965, the expected count for 118 would be 1986.
With the limit at 117 the statement returns in a few hundred milliseconds. When I put the limit up to 118 the query just sits there and doesn't return at all.
This is using an RDS Postgres instance, and running statements through IntelliJ. I've also read around that using large WHERE IN statements aren't as performant as joining on the ids through a temporary table instead, so I've edited the statement to use WITH, but the same hang between 117 and 118 exists.
I assume I'm hitting some constraint/limit with something more to do with hardware than the statement, I'm just not sure what with.
Edit: When I change the
WHERE clause to be
WHERE customer_id between 1 and 1000 the query is still really fast, and ids between 1 and 1000 have no gaps.
I've also since changed my RDS instance from a medium to an XL and I can now limit from 128, but 129 hangs. Nothing looks strange from the monitoring.