From: Benoit Delbosc <bdelbosc@nuxeo.com>
To: pgsql-performance@postgresql.org
Subject: Bad query plan inside EXISTS clause
Date: Wed, 10 Mar 2010 14:26:20 +0100
Message-ID: <4B979DFC.7080509@nuxeo.com> (raw)
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: bdelbosc@nuxeo.com
Subject: Re: Bad query plan inside EXISTS clause
In-Reply-To: <4B979DFC.7080509@nuxeo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox