Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
Indexes:
    "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
share|improve this question
1  
Where do these long lists of id's come from? Are they stored/calculated outside of the database? –  j.p. Feb 10 '12 at 13:04
    
The IDs are returned from Twitter as the list of followers a person has. –  François Beausoleil Feb 10 '12 at 13:20
add comment

1 Answer 1

up vote 3 down vote accepted

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:

CREATE TEMP TABLE t AS 
  SELECT * FROM (VALUES(1),(2),(3)) x(twitter_user_id);

SELECT 
  twitter_personas.* 
FROM twitter_personas 
  JOIN t USING(twitter_user_id);

Use EXPLAIN to see the difference between the queryplans.

share|improve this answer
    
+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 ??? –  RolandoMySQLDBA Feb 10 '12 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. –  Frank Heikens Feb 10 '12 at 7:21
    
I'll try the temporary table route. –  François Beausoleil Feb 10 '12 at 13:21
1  
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. –  Erwin Brandstetter Apr 12 '12 at 17:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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