I have a query of the following form:

SELECT * FROM twitter_personas WHERE twitter_user_id IN ($1, $2, $3, ..., $25000)

The IN query has anywhere from 10 to 25000 values. The query runs for minutes at a time. I have a backlog of nearly 500,000 queries like this to run.

The twitter_user_id column is indexed. Any ideas on how I could speed this up?

# \d twitter_personas
                                    Table "public.twitter_personas"
      Column      |          Type          |                         Modifiers                          
 persona_id       | uuid                   | not null
 twitter_user_id  | bigint                 | 
 screen_name      | character varying(40)  | not null
 avatar_url       | text                   | 
 hashval          | integer                | not null default nextval('personas_hashval_seq'::regclass)
    "twitter_personas_pkey" PRIMARY KEY, btree (persona_id)
    "index_twitter_personas_on_screen_name" UNIQUE, btree (screen_name)
    "index_twitter_personas_on_screen_name_persona_id" btree (screen_name, persona_id)
    "index_twitter_personas_twitter_user_id" btree (twitter_user_id) WHERE twitter_user_id IS NOT NULL
  • 1
    Where do these long lists of id's come from? Are they stored/calculated outside of the database?
    – j.p.
    Commented Feb 10, 2012 at 13:04
  • The IDs are returned from Twitter as the list of followers a person has. Commented Feb 10, 2012 at 13:20

2 Answers 2


IN() using many parameters will result in many cases in a sequential table scan. That might be slow, depending on table size and speed of your system.

Create a temporary table with all your variables and join on this table:

  SELECT * FROM (VALUES(1),(2),(3)) x(twitter_user_id);

FROM twitter_personas 
  JOIN t USING(twitter_user_id);

Use EXPLAIN to see the difference between the queryplans.

  • +1 for your ingenuity. I have a question though: Would a LEFT OUTER JOIN and RIGHT OUTER JOIN produce different query plans, or would there be no significant differenece ??? Commented Feb 10, 2012 at 6:57
  • 1
    @RolandoMySQLDBA: You have to check the results from EXPLAIN, but a LEFT or RIGHT join will result in different results than the current IN() or INNER JOIN. Maybe it's also possible to solve this performance problem using a Common Table Expression, but I didn't check it with many values nor a prepared statement. Commented Feb 10, 2012 at 7:21
  • I'll try the temporary table route. Commented Feb 10, 2012 at 13:21
  • 3
    A CTE with the same VALUE row constructor will work just as well. Even faster - it is basically a temporary table, just with less overhead. Commented Apr 12, 2012 at 17:37
  • A CTE is better solution. Commented Jan 12, 2015 at 17:21

Another option is to use ANY.

 SELECT * FROM twitter_personas 
 WHERE twitter_user_id ANY (VALUES  ($1), ($2), ($3), ..., ($25000))

I ran a query with 1000 parameters and it took 4 minutes using IN and 1 second using ANY. You can also use ANY ARRAY Rather than ANY VALUES but from my reading this is slower than ANY VALUES.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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