From: Alberto Marchesini <alberto.marchesini@stdm.net>
To: pgsql-performance@postgresql.org
Subject: BAD performance with enable_bitmapscan = on with Postgresql 9.0.X (X = 3 and 10)
Date: Fri, 26 Oct 2012 17:30:23 +0200
Message-ID: <508AAC8F.10108@stdm.net> (raw)
Hi,
I have a tree-structure managed with ltree and gist index.
Simplified schema is
CREATE TABLE crt (
idcrt INT NOT NULL,
...
pathname LTREE
)
idcrt primary key and other index ix_crt_pathname on pathname with gist
CREATE TABLE doc (
iddoc INT NOT NULL, ...)
iddoc primary key
CREATE TABLE folder_document (
id_folder int not null,
id_document int not null,
...
path_folder ltree not null
);
id_folder , id_document are primary key
ix_folder_document_path_folder on path_folder with gist
when enable_bitmapscan is set on query go on 1000 seconds, when I turned
off bitmapscan query go on 36 seconds.
I've noticed query use all buffer with ix_folder_document_path_folder,
using contrib pg_buffercache.
Table crt have about 1.3 milion row folder_document 15 milion row and doc
about 8 milion row.
Query plan with enable_bitmapscan = ON is http://explain.depesz.com/s/d97
Query plan with enable_bitmapscan = OFF is http://explain.depesz.com/s/wgp
All query are execute after reboot machine.
other parameter set
shared_buffer = 1GB
work_mem = 128MB
maintenance_work_mem = 512MB
effective_cache_size = 1GB
I've test same query on PostgreSQL 9.1.5 and query go ok with
enable_bitmapscan = on.
I see in release note 9.0.5
"Fix performance problem when constructing a large, lossy bitmap", is same
problem with 9.0.10?
My enviroment
Linux OpenSUSE 12.2 x64
PostgreSQL release 9.0.10 compiled from source
PostgreSQL release 9.1.5 from official repository
Processor Intel Core i5 2.8GHz and 8GB RAM
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: alberto.marchesini@stdm.net
Subject: Re: BAD performance with enable_bitmapscan = on with Postgresql 9.0.X (X = 3 and 10)
In-Reply-To: <508AAC8F.10108@stdm.net>
* 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