From: Benoit Delbosc <bdelbosc@nuxeo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad query plan inside EXISTS clause
Date: Wed, 10 Mar 2010 17:59:38 +0100
Message-ID: <4B97CFFA.6030000@nuxeo.com> (raw)
In-Reply-To: <15947.1268235860@sss.pgh.pa.us>
References: <4B979DFC.7080509@nuxeo.com>
<15947.1268235860@sss.pgh.pa.us>
Tom Lane a écrit :
> Benoit Delbosc <bdelbosc@nuxeo.com> writes:
>> I am trying to understand why inside an EXISTS clause the query planner
>> does not use the index:
>
> I'm not sure this plan is as bad as all that. The key point is that the
> planner is expecting 52517 rows that match that users_md5 value (and the
> true number is evidently 51446, so that estimate isn't far off). That's
> about 1/48th of the table. It knows that the EXISTS case can stop as
> soon as it finds one match, so it's betting that a plain seqscan will
> hit a match faster than an index lookup would be able to, ie,
> seqscanning about 48 tuples is faster than one index lookup. This might
> be a bad bet if the users_md5 values are correlated with physical order,
> ie the matches are not randomly scattered but are all towards the end of
> the table.
exact, the data is not randomly scattered but ordered this explains why
in my case seq scan is a bad bet
Barring that, though, it could be a good bet if the table
> isn't swapped in. Which is what the default cost parameters are set
> up to assume.
there are lots of shared buffers and effective memory on this instance,
the query is executed many times I can assume that the table isn't
swapped in right ?
> I suspect your real complaint is that you expect the table to be swapped
> in, in which case what you ought to be doing is adjusting the planner's
> cost parameters. Some playing around here with a similar case suggests
> that even a small reduction in random_page_cost would make it prefer an
> indexscan for this type of situation.
excellent !
Changing the random_page_cost from 4 to 2 do the trick
SET random_page_cost = 2;
EXPLAIN ANALYZE SELECT 1 WHERE EXISTS (SELECT 1 FROM read_acls_cache
WHERE users_md5 = '9bc9012eb29c0bb2ae3cc7b5e78c2acf');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=1.06..1.07 rows=1 width=0) (actual time=0.048..0.048
rows=1 loops=1)
One-Time Filter: $0
InitPlan 1 (returns $0)
-> Index Scan using read_acls_cache_users_md5_idx on
read_acls_cache (cost=0.00..55664.21 rows=52517 width=0) (actual
time=0.045..0.045 rows=1 loops=1)
Index Cond: ((users_md5)::text =
'9bc9012eb29c0bb2ae3cc7b5e78c2acf'::text)
Total runtime: 0.087 ms
(6 rows)
466/0.087 = 5360 thanks !
kind regards
ben
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, tgl@sss.pgh.pa.us
Subject: Re: Bad query plan inside EXISTS clause
In-Reply-To: <4B97CFFA.6030000@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