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.

  • Try to avoid more nested "select" statement in where clause. – Md Haidar Ali Khan Sep 21 '17 at 13:55
  • @MdHaidarAliKhan: Funny, just posted an answer along that line. – Erwin Brandstetter Sep 21 '17 at 13:56
  • 3
    The question needs information as instructed in the tag info to [postgresql-performance]. In particular, the output of EXPLAIN (ANALYZE, BUFFERS) <query> for LIMIT 128 and EXPLAIN <query> for LIMIT 129. – Erwin Brandstetter Sep 21 '17 at 14:19
  • Note that there's no guarantee what IDs are returned from select id from customer limit 117. It's not necessarily ID values 1 through 117. If you want the first 117 IDs, you should add ORDER BY id to the subquery. This may affect the performance issue, though I assume it will not. – RDFozz Sep 21 '17 at 16:37

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?


| improve this answer | |
  • Thanks for the reply. Originally the statement was a where with 1000 customer ids (actually listed out sequentially, I changed it to the use of limits for the example and so I could debug to the hanging point easier). I then did change it to a join, and then to a WITH statement as both of your examples suggest - and what was suggested elsewhere for optimizing. Both scenarios still had the same issue though which is why I came here for tips on how to diagnose the issue. Your point about query plans is helpful so I'll take a look in that direction. – Firepanda Sep 21 '17 at 16:39

You should rewrite this as an EXISTS clause

SELECT count(*)
FROM (joins)
  FROM customer AS c
  WHERE outer.customer_id = c.customer_id
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.