Re: Bad query plan inside EXISTS clause

От: Kenneth Marshall
Тема: Re: Bad query plan inside EXISTS clause
Дата: ,
Msg-id: 20100310134342.GA29320@it.is.rice.edu
(см: обсуждение, исходный текст)
Ответ на: Bad query plan inside EXISTS clause  (Benoit Delbosc)
Ответы: Re: Bad query plan inside EXISTS clause  (Grzegorz Jaśkiewicz)
Re: Bad query plan inside EXISTS clause  (Yeb Havinga)
Список: pgsql-performance

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

Bad query plan inside EXISTS clause  (Benoit Delbosc, )
 Re: Bad query plan inside EXISTS clause  (Kenneth Marshall, )
  Re: Bad query plan inside EXISTS clause  (Grzegorz Jaśkiewicz, )
  Re: Bad query plan inside EXISTS clause  (Yeb Havinga, )
   Re: Bad query plan inside EXISTS clause  (Yeb Havinga, )
    Re: Bad query plan inside EXISTS clause  (Benoit Delbosc, )
 Re: Bad query plan inside EXISTS clause  (Tom Lane, )
  Re: Bad query plan inside EXISTS clause  (Benoit Delbosc, )

EXISTS matches NULLs too and since they are not indexed a
sequential scan is needed to check for them. Try using
IN instead.

Cheers,
Ken

On Wed, Mar 10, 2010 at 02:26:20PM +0100, Benoit Delbosc wrote:
> Hi all,
>
> I am trying to understand why inside an EXISTS clause the query planner
> does not use the index:
>
> EXPLAIN ANALYZE SELECT 1 WHERE EXISTS (SELECT 1 FROM read_acls_cache
>      WHERE users_md5 = '9bc9012eb29c0bb2ae3cc7b5e78c2acf');
>                                         QUERY PLAN
> --------------------------------------------------------------------------------------------
>  Result  (cost=1.19..1.20 rows=1 width=0) (actual time=466.317..466.318
> rows=1 loops=1)
>    One-Time Filter: $0
>    InitPlan 1 (returns $0)
>      ->  Seq Scan on read_acls_cache  (cost=0.00..62637.01 rows=52517
> width=0) (actual time=466.309..466.309 rows=1 loops=1)
>            Filter: ((users_md5)::text =
> '9bc9012eb29c0bb2ae3cc7b5e78c2acf'::text)
>  Total runtime: 466.369 ms
> (6 rows)
>
> While it does use the index when executing only the subquery:
>
> EXPLAIN ANALYZE SELECT 1 FROM read_acls_cache WHERE users_md5 =
> '9bc9012eb29c0bb2ae3cc7b5e78c2acf';
>                             QUERY PLAN
> --------------------------------------------------------------------------
>  Bitmap Heap Scan on read_acls_cache  (cost=2176.10..35022.98 rows=52517
> width=0) (actual time=9.065..21.988 rows=51446 loops=1)
>    Recheck Cond: ((users_md5)::text =
> '9bc9012eb29c0bb2ae3cc7b5e78c2acf'::text)
>    ->  Bitmap Index Scan on read_acls_cache_users_md5_idx
> (cost=0.00..2162.97 rows=52517 width=0) (actual time=8.900..8.900
> rows=51446 loops=1)
>          Index Cond: ((users_md5)::text =
> '9bc9012eb29c0bb2ae3cc7b5e78c2acf'::text)
>  Total runtime: 25.464 ms
> (5 rows)
>
> The table has been vacuumed, analyzed and reindexed.
>
> Thanks for your support.
>
> Regards
>
> ben
>
> Here are some more info :
>
> \d read_acls_cache
>         Table "public.read_acls_cache"
>   Column   |         Type          | Modifiers
> -----------+-----------------------+-----------
>  users_md5 | character varying(34) | not null
>  acl_id    | character varying(34) |
> Indexes:
>     "read_acls_cache_users_md5_idx" btree (users_md5)
>
>
> SELECT COUNT(*) FROM read_acls_cache;
>   count
> ---------
>  2520899
> (1 row)
>
>
> SELECT COUNT(DISTINCT(users_md5)) FROM read_acls_cache ;
>  count
> -------
>     49
> (1 row)
>
>
> SELECT Version();
>                                                   version
> ------------------------------------------------------------------
>  PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.2.real
> (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu4), 64
> (1 row)
>
>
>
> --
> Sent via pgsql-performance mailing list ()
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>


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

От: Benoit Delbosc
Дата:
Сообщение: Re: Bad query plan inside EXISTS clause
От: Robert Haas
Дата:
Сообщение: Re: Strange workaround for slow query