Postgresql – WHERE IN query to very large table is slow

performancepostgresqlqueryquery-performance

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

Best Answer

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

Related Solutions

Postgresql – Postgres 8.4.5: Bad query plan vs Good query plan when changing date range on large data table

This is a "community wiki" answer provided by the OP, which I've removed from the question.

SOLVED.

I was able to resolve this issue by altering the time.date column to allow 1000 stats: ALTER TABLE time ALTER COLUMN date SET STATISTICS 1000;

Now the queries are running very fast, down to 300ms from 5s.

I'll be looking into patching postgres as well.

Mysql – Very Slow Query

It seems to me you just need to use the GROUP_CONCAT aggregate function:

SET @JSON :=
  CONCAT(
    '[',
    (
      SELECT
        GROUP_CONCAT(
          CONCAT(
            '["', ctd.BidID,
            '","', REPLACE(REPLACE(ctd.Description,           '"', '\\"'),"'", "\\'"), 
            '","', REPLACE(REPLACE(ctd.FoundationDescription, '"', '\\"'),"'", "\\'"), 
            '","', REPLACE(REPLACE(ctd.EngDrawingNumber,      '"', '\\"'),"'", "\\'"), 
            '","', REPLACE(REPLACE(ctd.DetailDrawingNumber,   '"', '\\"'),"'", "\\'"),  
            '","', ctd.TakeOffQuantity, 
            '"]'
          )
          ORDER BY <some-column>
        )
      FROM
        `Job1111`.CivilTrackerDetails AS ctd
    ),
    ']'
  )
;

As pointed out by Rick James, applying GROUP_CONCAT to an entire table like that might result in exceeding the default group_concat_max_len value, which is 1024 in MySQL5.7. So, make sure you have set it big enough to accommodate the resulting string by, for instance, setting it session-wise using a SET statement just before the string assignment, like this:

SET group_concat_max_len = 20480;  -- for example
SET @JSON := ...

Related Question