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
