public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bruce Momjian <[email protected]>
To: Daniel Westermann (DWE) <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Instead of using the bloom index, a parallel sequencial scan is used with this example
Date: Tue, 5 Nov 2019 18:18:54 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <VI1PR09MB4142FC41A11AE3FFAA52B1F9D27E0@VI1PR09MB4142.eurprd09.prod.outlook.com>
References: <[email protected]>
<[email protected]>
<VI1PR09MB4142FC41A11AE3FFAA52B1F9D27E0@VI1PR09MB4142.eurprd09.prod.outlook.com>
On Tue, Nov 5, 2019 at 08:28:57PM +0000, Daniel Westermann (DWE) wrote:
>
> >>On Thu, Oct 24, 2019 at 03:17:06PM +0000, PG Doc comments form wrote:
> >> The following documentation comment has been logged on the website:
> >>
> >>Page: https://www.postgresql.org/docs/12/bloom.html
> >> Description:
>
> >I cleaned up your script and created an SQL file that can be piped into
> >psql, attached. I see the bloomidx index being used without and with
> >the ANALYZE, output attached. I tested this on git master, and back
> >through PG 10. Would you please run these queries and post the output:
>
> Thanks for having a look, Bruce. You test case is not exactly the same as in
> the documentation. For you "temporary table" test case I indeed see the bloom
> index getting used. Doing the same with a normal table results in a parallel
> seq scan.
Ah, I see your point about the temporary tables. I have retested with
permanent tables, and see the same output you do. By reducing the
generate_series by 100x, I am able to get the desired EXPLAIN plans by
turning on/off various optimizer settings. I am attaching my test
script and its output.
I suggestion is what I reduce generate_series by 100x and update the
EXPLAIN plans in the docs. The docs are unclear on how these different
plans are generated, though I am not excited about adding the exact
optimizer settings to generate each plan.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
DROP TABLE
SELECT 100000
CREATE EXTENSION
CREATE INDEX
CREATE INDEX
ANALYZE
SET
SET
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on tbloom (cost=0.00..2137.00 rows=1 width=24) (actual time=12.228..12.228 rows=0 loops=1)
Filter: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Filter: 100000
Planning Time: 0.264 ms
Execution Time: 12.249 ms
(5 rows)
SET
RESET
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Only Scan using btreeidx on tbloom (cost=0.42..2992.43 rows=1 width=24) (actual time=6.462..6.463 rows=0 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Heap Fetches: 0
Planning Time: 0.102 ms
Execution Time: 6.484 ms
(5 rows)
RESET
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbloom (cost=1792.00..1796.02 rows=1 width=24) (actual time=0.438..0.438 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Index Recheck: 31
Heap Blocks: exact=29
-> Bitmap Index Scan on bloomidx (cost=0.00..1792.00 rows=1 width=0) (actual time=0.394..0.394 rows=31 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Planning Time: 0.070 ms
Execution Time: 0.461 ms
(8 rows)
Attachments:
[application/x-sql] bloom.sql (886B, 2-bloom.sql)
download
[text/plain] out.txt (1.7K, 3-out.txt)
download | inline:
DROP TABLE
SELECT 100000
CREATE EXTENSION
CREATE INDEX
CREATE INDEX
ANALYZE
SET
SET
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on tbloom (cost=0.00..2137.00 rows=1 width=24) (actual time=12.228..12.228 rows=0 loops=1)
Filter: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Filter: 100000
Planning Time: 0.264 ms
Execution Time: 12.249 ms
(5 rows)
SET
RESET
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Only Scan using btreeidx on tbloom (cost=0.42..2992.43 rows=1 width=24) (actual time=6.462..6.463 rows=0 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Heap Fetches: 0
Planning Time: 0.102 ms
Execution Time: 6.484 ms
(5 rows)
RESET
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbloom (cost=1792.00..1796.02 rows=1 width=24) (actual time=0.438..0.438 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Index Recheck: 31
Heap Blocks: exact=29
-> Bitmap Index Scan on bloomidx (cost=0.00..1792.00 rows=1 width=0) (actual time=0.394..0.394 rows=31 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Planning Time: 0.070 ms
Execution Time: 0.461 ms
(8 rows)
view thread (4+ messages)
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Instead of using the bloom index, a parallel sequencial scan is used with this example
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox