0

I currently have a table with around 300,000,000 rows with the following columns: user_id, item_id, item_value, and item_add_date

I also have a very long list of about 7000 user_id values which are associated with a date range.

I'm trying to query the really large table in order to obtain all rows where the user_id is in my list and where the item_add_date falls within the provided range.

My current approach is programatically looping through the list of ids and running the following query:

FROM all_items 
WHERE user_id in [insert_user_id_programatically] 
AND item_add_date >= [insert_start_date] 
AND item_add_date <= [insert_end_date]

which returns the results I want but takes about one hour per query, and for 7000 queries would take almost a year to run. Is there a faster way of achieving this? I'm not familiar with SQL and do most of the data manipulation for this project in python normally.

EDIT: Here is the output for EXPLAIN (ANALYZE, BUFFERS, VERBOSE): https://pastebin.com/GYL8JWk4

  • Thanks for the quick reply! I've just added the output (the column names are a bit different because I've renamed the variables in my post to help make it easier to understand). Also apologies in advance as I couldn't figure out how to format the text properly – Ray A. Mar 23 at 10:50
  • Unfortunately you messed up the formatting of the plan. The newlines and indention are crucial to reading and understanding it. (and a plan generated as suggested does not start with a [( - how and where did you run that? – a_horse_with_no_name Mar 23 at 10:54
  • I ran the query with EXPLAIN (ANALYZE, BUFFERS, VERBOSE) using psycopg2 and printed the output. I'll try and work out how to format it correctly – Ray A. Mar 23 at 11:22
  • use a proper SQL client, e.g. psql – a_horse_with_no_name Mar 23 at 11:28
  • 1
    If you don't care about the performance under LIMIT, then please don't show us the EXPLAIN plan for it with the LIMIT. The query you explain should be the query you care about. – jjanes Mar 23 at 13:00
1

Sometimes large in clauses can be improved by rewriting them as a join against a values clause.

so instead of

select *
from all_items
where user_id in (1,2,3,4,5)
 ...

you can use

select ai.*
from all_items ai
   join ( 
       values (1),(2),(3),(4),(5)
   ) t(id) on t.id = ai.user_id
where item_add_date >= ...
  and item_add_date <= ...;

An index on (user_id, item_add_date) should improve the performance

  • With the join ( values (1), (2) ...,), is there a way to run each value (1, 2, 3, 4, 5) as a separate query? Currently there are about 7000 IDs stored in a separate file so I was thinking it would be easier to loop through all of them and run a separate query for each. If that's feasible, what would the query look like? Thanks so much for all your help! – Ray A. Mar 23 at 10:52
  • No, running one query with 7000 IDs will be much faster than 7000 individual queries. Note the values should be (1),(2),(...), .. not (1,2,...) – a_horse_with_no_name Mar 23 at 10:55
  • Right. Using that format, what would be the syntax for the various dates in this bit: "where item_add_date >= ... and item_add_date <= ...;"?. E.g. would it be something like this: "where item_add_date >= (1/2/2019, 2/5/2019...)"? – Ray A. Mar 23 at 11:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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