Why does my query hang when I put one more id in where statement? [closed]

Question

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.

Answer 1

You are probably exhausting some resource (adequate resource settings?) and Postgres starts swapping out to disk. And / or, more likely, the query planner switches to a different query plan, based on your cost settings (which may be configured inadequately) and table statistics (which may be outdated).

I can't be more specific, information is missing.

All these possible problems in your setup aside, assuming customer.id is a unique column, this equivalent query with a JOIN replacing in the IN (subquery) should be considerably faster:

SELECT count(*)
FROM   (SELECT id FROM customer LIMIT 128) c  -- arbitrary rows! see below
JOIN   joins j ON j.customer_id = c.id        -- resolve "joins" properly
WHERE  -- ... (more expressions)

Or put the subquery in a CTE. The important point is the join instead of IN. Like:

WITH cids AS (SELECT id FROM customer LIMIT 128)  -- arbitrary rows!
SELECT count(*)
FROM   cids c  
JOIN   ...

Adapt the join clause, depending on what's behind (joins) in your original query.

Plus, be aware that LIMIT without ORDER BY selects arbitrary rows. So due to internal effects, the subquery with LIMIT 128 can return completely different IDs from the one with LIMIT 129 (even for the same LIMIT), which can result in a completely different count. Is that what you want?

Related:

• SQL sorting by 0
• Complex view becomes slow when adding ORDER BY in outer query with small LIMIT

Answer 2

You should rewrite this as an EXISTS clause

SELECT count(*)
FROM (joins)
WHERE EXISTS (
  SELECT 1
  FROM customer AS c
  WHERE outer.customer_id = c.customer_id
);