Excluding rows by lots of ids I need to exclude some rows that the user has already seen. Currently I am doing this by simply comparing the ids that the user has seen to the row's ids by running this query (there are also other fields to filter by that I left out):

SELECT * FROM table WHERE NOT (id IN (ids here)) LIMIT 18;


However, since I have about 150,000 rows in my table, and the amount of ids can be even up to 30,000, this is unbearably slow.

I haven't worked a lot with databases before, so I'm probably doing this completely wrong. Is there any better way to achieve this?

I'm running PostgreSQL 9.3.9

Any help is greatly appreciated!
NOT IN can have slower performance than a left join for some data sets. You can see an example here that compares different ways of excluding items from your selection. Ideally you could identify the IDs you want to include, and this would definitely gain better performance.

So first you could try one of the two query changes depending on your data:

SELECT *
FROM Table
WHERE ID IN (SELECT IDs FROM UserViewingList WHERE UserID = @UserID)


If you're using an app of some sort, you may be able to update a viewing table while the user chews through the first set of data, and then have that available for a quicker response when they request the next set.

Or if you don't have that option/data try replacing NOT IN:

SELECT BaseTable.*
FROM BaseTable
LEFT JOIN ViewedIDs
     ON BaseTable.ID = ViewedIDs.ID
WHERE ViewedIDs.ID IS NULL


The other question though is can you cache this set in an App or is there another way to update the list? You might be able to add pagination or some other tool in your UI that can handle the selection of IDs in memory on a per user basis.

In addition to the query changes, you can add indexing to your User IDs column, if not already indexed (on your source table - BaseTable in the example above).