public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bruce Momjian <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: Instead of using the bloom index, a parallel sequencial scan is used with this example
Date: Tue, 5 Nov 2019 15:02:51 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
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:
SELECT version();
SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');
My guess is that you have some non-default setting that is causing
bloomidx not to be used.
--
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 +
SELECT 10000000
CREATE INDEX
CREATE INDEX
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbloom (cost=178436.06..179392.83 rows=250 width=24) (actual time=98.114..98.114 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Index Recheck: 2375
Heap Blocks: exact=2317
-> Bitmap Index Scan on bloomidx (cost=0.00..178436.00 rows=250 width=0) (actual time=86.796..86.797 rows=2375 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Planning time: 0.326 ms
Execution time: 98.156 ms
(8 rows)
ANALYZE
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbloom (cost=178434.71..178438.73 rows=1 width=24) (actual time=104.982..104.982 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Index Recheck: 2375
Heap Blocks: exact=2317
-> Bitmap Index Scan on bloomidx (cost=0.00..178434.71 rows=1 width=0) (actual time=93.657..93.657 rows=2375 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Planning time: 0.215 ms
Execution time: 105.018 ms
(8 rows)
Attachments:
[application/x-sql] bloom.sql (617B, 2-bloom.sql)
download
[text/plain] out.txt (1.5K, 3-out.txt)
download | inline:
SELECT 10000000
CREATE INDEX
CREATE INDEX
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbloom (cost=178436.06..179392.83 rows=250 width=24) (actual time=98.114..98.114 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Index Recheck: 2375
Heap Blocks: exact=2317
-> Bitmap Index Scan on bloomidx (cost=0.00..178436.00 rows=250 width=0) (actual time=86.796..86.797 rows=2375 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Planning time: 0.326 ms
Execution time: 98.156 ms
(8 rows)
ANALYZE
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tbloom (cost=178434.71..178438.73 rows=1 width=24) (actual time=104.982..104.982 rows=0 loops=1)
Recheck Cond: ((i2 = 898732) AND (i5 = 123451))
Rows Removed by Index Recheck: 2375
Heap Blocks: exact=2317
-> Bitmap Index Scan on bloomidx (cost=0.00..178434.71 rows=1 width=0) (actual time=93.657..93.657 rows=2375 loops=1)
Index Cond: ((i2 = 898732) AND (i5 = 123451))
Planning time: 0.215 ms
Execution time: 105.018 ms
(8 rows)
view thread (4+ messages) latest in thread
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