Message-ID: <4B979DFC.7080509@nuxeo.com>
Date: Wed, 10 Mar 2010 14:26:20 +0100
From: Benoit Delbosc <bdelbosc@nuxeo.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Bad query plan inside EXISTS clause
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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)