Re: Slow query with sub-select

On Jul 16, 2011, at 6:32, - - <loh(dot)law(at)hotmail(dot)com> wrote:

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)

1. Indexes are not magical; their usage does not guarantee a fast query2. It is slow because you have no non-join where condition and around 225 MILLION rows that need to be evaluated.3. Also, you are using a correlated sub-query instead of a LEFT OUTER JOIN4. You haven't provided table definitions with indexes and so whether q.mid=t.mid is a 1-to-1 optional relationship is unknowable. Hell, since the names are meaningless we cannot even guess what kind of relationship the tables should have. The generic "mid" field name has the same problem.
David J.

The weird thing is that before I updated my server the query was about 5 times faster.
I've googled and I think the problem lies with the under-estimation of the query planner about the number of rows in the nested table.I will be trying the 'set enable_seqscan = false' solution to see if that'll improve.