CODEWITHSUNDEEP
sqlpostgresqlheroku
Stephen Corwin
Stephen Corwin

Reputation: 1007

Postgres query slow on production, fast on both staging and local

I'm at a loss on this one. I have a fairly large query that looks like this:

SELECT *
FROM some_table
WHERE some_id IN (hundreds, perhaps even thousands of ids)

For some reason, while this query runs reasonably quickly for most of our users, we have one or two accounts for which is runs so slowly that we're hitting timeout errors (Heroku). I figured it must be related to those users' specific data, however, when I put a copy of the production database on our staging server, those same exact queries ran fast (< 2 seconds). I also tested the data on my local machine to the same effect: fast.

All three environments are running Postgres 11. I can't for the life of me figure out what's causing this.

Any thoughts?

Upvotes: 3

Views: 1178

Answers (2)

user330315
user330315

Reputation:

Very often joining to a values list is faster than an IN list:

SELECT st.*
FROM some_table st 
  JOIN ( 
     values (1), (2), (3), (4)
  ) t(id) on st.some_id = t.id;

Note the different format of the values list compared to the IN list. IN (1,2,3) becomes values (1),(2),(3)

Alternatively you can use a common table expression instead of a derived table

with id_list (id) as (
   values (1), (2), (3), (4)
) 
select st.*
from some_table st
  join id_list i on i.id = st.some_id;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

If your "hundreds, perhaps even thousands of ids" is a result for a query then you could avoid the in clause and use an inner join 

SELECT *
FROM some_table
INNER JOIN  (
  select id from my_table /* for hundreds, perhaps even thousands of ids */
) t on t.id = some_table.some_id

and be sure you have a proper index on table some_table column some_id

Upvotes: 2

Related Questions