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 (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
