Return to Answer

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 result. Is that what you want?

Related:

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

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

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)

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. Is that what you want?

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 result. Is that what you want?

Related:

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

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 should be considerably faster:

SELECT count(*)
FROM   (select id from customer limit 117) c
JOIN   joins j ON j.customer_id = c.id
WHERE  -- ... (more expressions)

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

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)

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. Is that what you want?