From: | - - <loh(dot)law(at)hotmail(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Slow query with sub-select |
Date: | 2011-07-16 10:32:33 |
Message-ID: | BAY147-W391CC9FFE32010F27DE2D2F0480@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 --------------------------------------------------------------------------------------------------------- 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=3641527 width=10) -> Index Scan using t_pkey on t (cost=0.00..7083958.46 rows=3641527 width=10)(6 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2011-07-16 14:06:25 | Re: Slow query with sub-select |
Previous Message | Deniz Atak | 2011-07-16 08:47:50 | Table dublicates values |