Optimize tuning for read performance
Your memory settings seem reasonable for a server with 64 GB, with the possible exception of work_mem = 512MB . This is high. Your queries are not particularly complex, and your tables are not so large.
4.5 million rows (300k x 15) in a simple documents_tags connection table should occupy ~ 156 MB and PK another 96 MB. For your query, you usually do not need to read the entire table, only small parts of the index. For " mostly read-only ", as with you, you should only see indexing only in the PK index. You don't need nearly as much work_mem - which might not really matter - unless you have a lot of concurrent requests. Quote guide :
... several work sessions can perform such operations simultaneously. Therefore, the total used memory can be many times greater than the value of work_mem ; this must be considered when choosing a value.
Setting work_mem too high can damage performance:
I suggest reducing work_mem to 128 MB or less to avoid possible memory starvation - unless you have other general requests that require more. You can always install it locally for special requests.
There are several other angles for optimizing reading performance:
Main issue: leading index column
All of this may help a little. But the main problem is this:
PRIMARY KEY (document_id, tag_id )
300k documents, 2 tags for exclusion. Ideally, you have an index with tag_id as the leading column and document_id as the second. With an index only (tag_id) you cannot get only indexing. If this request is your only use case, change your PC as shown below.
Or perhaps even better: you can create an additional simple index on (tag_id, document_id) if you need both - and omit the other two indexes on documents_tags only on (tag_id) and (document_id) . They do not offer anything over two multi-column indexes. The remaining indices 2 (in contrast to indices 3 ) are smaller and superior to all. Justification:
At the same time, I also propose a CLUSTER table using the new PK, all in one transaction, possibly with some additional maintenance_work_mem locally:
BEGIN; SET LOCAL maintenance_work_mem = '256MB'; ALTER TABLE documents_tags DROP CONSTRAINT documents_tags_pkey , ADD PRIMARY KEY ( tag_id , document_id);
Do not forget:
ANALYZE documents_tags;
Inquiries
The request itself is launched. Here are four standard methods:
NOT IN - to quote:
Only for small sets with no null values
Your usage example: all involved columns are NOT NULL , and the list of excluded items is very short. Your original request is an ardent contender.
NOT EXISTS and LEFT JOIN / IS NULL always hot contenders. Both were suggested in other answers. LEFT JOIN must be the actual LEFT [OUTER] JOIN .
EXCEPT ALL will be shorter, but often not so fast.
1. DOES NOT INCLUDE
SELECT document_id FROM documents d WHERE document_id NOT IN ( SELECT document_id
2. DOES NOT EXIST
SELECT document_id FROM documents d WHERE NOT EXISTS ( SELECT 1 FROM documents_tags WHERE document_id = d.document_id AND tag_id IN (12345, 7654) );
3. LEFT JOIN / IS NULL
SELECT d.document_id FROM documents d LEFT JOIN documents_tags dt ON dt.document_id = d.document_id AND dt.tag_id IN (12345, 7654) WHERE dt.document_id IS NULL;
4. EXCEPT ALL
SELECT document_id FROM documents EXCEPT ALL
Benchmark
I ran a quick test on my old laptop with 4 GB of RAM and Postgres 9.5.3 to test my theories:
Test setup
SET random_page_cost = 1.1; SET work_mem = '128MB'; CREATE SCHEMA temp; SET search_path = temp, public; CREATE TABLE documents ( document_id serial PRIMARY KEY, title text );
Note that the lines in documents_tags physically grouped using document_id due to the way I populated the table, which is also likely in your current situation.
Test
3 test runs with each of 4 queries, best of all 5 times, to exclude caching effects.
Test 1: With documents_tags_pkey as you have. The index and physical order of the rows is bad for our query.
Test 2: Create a PK on (tag_id, document_id) as suggested.
Test 3: CLUSTER on the new PC. EXPLAIN ANALYZE execution time in ms:
time in ms | Test 1 | Test 2 | Test 3
1. NOT IN | 654 | 70 | 71 - winner!
2. NOT EXISTS | 684 | 103 | 97
3. LEFT JOIN | 685 | 98 | 99
4. EXCEPT ALL | 833 | 255 | 250
conclusions
The key element is the right index with the leading tag_id - for queries with multiple tag_id and many document_id .
To be precise, it doesnโt matter that there is a more distinct document_id than tag_id . It could be the other way around. Btree indexes basically do the same with any column order. This is the fact that the most selective predicate in your query filters is tag_id . And it is faster on leading index columns.
The winning request for multiple tag_id for exception is your original with NOT IN .
NOT EXISTS and LEFT JOIN / IS NULL result in the same query plan. For more than a few dozen identifiers, I expect them to scale better ...
In a read-only situation, you will only see checking only indexes , so the physical order of the rows in the table becomes irrelevant. Therefore, test 3 did not bring any additional improvements.
If writing to the table occurs and autovacuum cannot keep up, you will see a (raster) scan of the index . Physical clustering is important to them.