The following query seems to take ages despite the EXPLAIN stating that an index is used.
Also, the condition (WHERE t.mid = q.mid) should be a one-to-one mapping, should it not? In this case the mapping is to 3641527 rows.
Table q has no indexes and not referenced by other tables.
Table t has an index on column mid.
Does anyone know why the query is slow?
SELECT COUNT(*) FROM q
WHERE NOT EXISTS (SELECT 1
FROM t AS t
WHERE t.mid = q.mid);
QUERY PLAN
< font class="Apple-style-span" face="Tahoma" size="2">---------------------------------------------------------------------------------------------------------
Aggregate (cost=10021304028.93..10021304028.94 rows=1 width=0)
-> Hash Anti Join (cost=10007145480.54..10021028896.24 rows=110053073 width=0)
Hash Cond: ((q.mid)::text = (t.mid)::text)
-> Seq Scan on q (cost=10000000000.00..10007993328.46 rows=220106146 width=38)
-> Hash (cost=7083958.46..7083958. 46 rows=364 1527 width=10)
-> Index Scan using t_pkey on t (cost=0.00..7083958.46 rows=3641527 width=10)
(6 rows)