BAD performance with enable_bitmapscan = on with Postgresql 9.0.X (X = 3 and 10)

From: Alberto Marchesini <alberto(dot)marchesini(at)stdm(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: BAD performance with enable_bitmapscan = on with Postgresql 9.0.X (X = 3 and 10)
Date: 2012-10-26 15:30:23
Message-ID: 508AAC8F.10108@stdm.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Browse pgsql-performance by date

  From Date Subject
Next Message Böckler Andreas 2012-10-26 15:30:47 Re: Query-Planer from 6seconds TO DAYS
Previous Message Kevin Grittner 2012-10-26 15:30:05 Re: Query-Planer from 6seconds TO DAYS