Postgres Professional
en

Slow query with sub-select

От: - -
Тема: Slow query with sub-select
Дата: ,
Msg-id: BAY147-W391CC9FFE32010F27DE2D2F0480@phx.gbl
(см: обсуждение, исходный текст)
Ответы: Re: Slow query with sub-select  (David Johnston)
Список: pgsql-general

Скрыть дерево обсуждения

Slow query with sub-select  (- -, )
 Re: Slow query with sub-select  (David Johnston, )
  Re: Slow query with sub-select  (- -, )
   Re: Slow query with sub-select  (Tom Lane, )
    Re: Slow query with sub-select  (- -, )
     Re: Slow query with sub-select  (Rick Genter, )
   Re: Slow query with sub-select  (Michael Nolan, )
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)

В списке pgsql-general по дате сообщения:

Предыдущее:
От: Tom Lane
Дата:
Сообщение: Re: Slow query with sub-select
Следующее:
От: Adrian Klaver
Дата:
Сообщение: Re: Table dublicates values