PostgreSQL添加索引后查询性能下降，SQLite中索引却优化查询性能的技术疑问

Let’s break down what’s happening here, why PostgreSQL and SQLite are behaving so differently, and how to get your PostgreSQL query back to lightning-fast speeds.

First, the Context #

I’m working with a 15M-row ncbitaxon table on PostgreSQL 13.8 (Debian 11), structured like this:

=> \d ncbitaxon
Table "public.ncbitaxon"
 Column     | Type    | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 assertion  | integer |           |          |
 retraction | integer |           |          | 0
 graph      | text    |           |          |
 subject    | text    |           |          |
 predicate  | text    |           |          |
 object     | text    |           |          |
 datatype   | text    |           |          |
 annotation | text    |           |          |

The goal is to find all subjects that:

1. Have a predicate = 'rdf:type' and object = 'owl:Class'
2. Don’t have any predicate = 'rdfs:subClassOf' entries

The query I’m using is:

select n1.subject 
from ncbitaxon n1 
where n1.predicate = 'rdf:type' and n1.object = 'owl:Class' 
and not exists ( 
    select 1 from ncbitaxon n2 
    where n2.subject = n1.subject and n2.predicate = 'rdfs:subClassOf' 
)

The Weird Performance Gap #

• PostgreSQL (no indexes): Runs in 2-3 seconds (perfect)
• PostgreSQL (with single-field B-tree indexes): Takes ~9 seconds (way too slow)
• SQLite 3.34.1: No indexes = query is so slow I have to kill it; with indexes = ~5 seconds (as expected)

The indexes I created are:

create index idx_77907_idx_ncbitaxon_predicate on ncbitaxon (predicate);
create index idx_77907_idx_ncbitaxon_subject on ncbitaxon (subject);
create index idx_77907_idx_ncbitaxon_object on ncbitaxon (object);
create index idx_77907_idx_ncbitaxon_datatype on ncbitaxon (datatype);

Why PostgreSQL and SQLite Act So Differently #

Let’s dig into the execution plans to understand this.

1. PostgreSQL’s Bad Plan Choice (With Indexes) #

When you add those single-field indexes, PostgreSQL’s optimizer picks a Nested Loop Anti Join. Here’s what happens:

• It does a parallel full scan on n1, filtering for rdf:type/owl:Class (~2.4M rows)
• For every one of those 2.4M rows, it runs an index scan on n2 using the subject index to check for rdfs:subClassOf
• Each index scan is fast, but doing 2.4M of them adds up—plus, each scan has to filter out 4 non-matching rows, which adds extra IO overhead.

Without indexes, PostgreSQL switches to a Parallel Hash Anti Join:

• It first scans n2 in parallel to collect all rdfs:subClassOf entries, builds a hash table in memory (or temp disk if needed)
• Then it scans n1 in parallel, filters for rdf:type/owl:Class, and does a hash lookup to exclude any subjects in the n2 hash table
• This avoids the millions of tiny index scans, so it’s way faster.

2. SQLite’s Index-Friendly Plan #

SQLite’s optimizer takes a different approach with indexes:

• It uses the object index to quickly find all owl:Class rows, then filters for rdf:type
• For each matching subject, it uses the subject index to check for rdfs:subClassOf
• SQLite’s query engine handles these nested lookups more efficiently than PostgreSQL’s in this scenario, and since it’s avoiding a full table scan entirely (unlike the no-index case), it’s way faster than without indexes.

3. Cost Model Differences #

PostgreSQL and SQLite calculate query costs differently. PostgreSQL’s cost model might be underestimating the total overhead of 2.4M index scans, or overestimating the memory/CPU cost of a hash join. SQLite’s model, on the other hand, prioritizes using indexes to reduce initial data volume, which makes sense for its lightweight use case.

How to Fix PostgreSQL’s Performance #

Let’s get that query back to sub-3-second speeds with better indexes and plan tuning.

1. Ditch Single-Field Indexes for Composite Indexes #

Single-field indexes are inefficient here—we need indexes that match our exact query conditions:

-- For the n1 filter: predicate + object, include subject to avoid table lookups (covering index)
create index idx_ncbitaxon_type_class on ncbitaxon (predicate, object) include (subject);

-- For the n2 check: subject + predicate, so we can directly find matching rows without filtering
create index idx_ncbitaxon_subclass on ncbitaxon (subject, predicate);

The first index lets PostgreSQL pull all matching subjects directly from the index (no need to hit the table). The second index lets it quickly check if a subject has a rdfs:subClassOf entry—no extra filtering needed.

2. Update Table Statistics #

Make sure PostgreSQL has fresh stats so it can pick the right plan:

analyze ncbitaxon;

3. Tweak Work Memory (If Needed) #

From the no-index plan, we can see it’s using temp disk for the hash join (temp written=11456). Increase work_mem to let the hash table fit in memory:

-- Session-level setting (adjust based on your server's RAM; 64MB is a safe start)
set work_mem = '64MB';

This cuts down on slow disk IO for the hash join.

4. Force a Hash Join (Temporary Fix) #

If the optimizer still picks nested loops after adding composite indexes, you can temporarily disable nested loops for the session:

set enable_nestloop = off;

Run your query, then turn it back on:

set enable_nestloop = on;

This is a band-aid, though—composite indexes should make the optimizer choose the right plan on its own.

5. Try Alternative Query Syntax #

Sometimes rewriting the query can help the optimizer:

-- Left Join + IS NULL (equivalent to NOT EXISTS)
select n1.subject
from ncbitaxon n1
left join ncbitaxon n2 
    on n1.subject = n2.subject 
    and n2.predicate = 'rdfs:subClassOf'
where n1.predicate = 'rdf:type' 
    and n1.object = 'owl:Class'
    and n2.subject is null;

-- NOT IN (note: avoid if subject can be NULL, since NOT IN treats NULLs differently)
select subject
from ncbitaxon
where predicate = 'rdf:type' and object = 'owl:Class'
and subject not in (
    select subject from ncbitaxon where predicate = 'rdfs:subClassOf'
);

内容的提问来源于stack exchange，提问作者The bassist