From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: 'Jeff Janes' <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Are bitmap index scans slow to start?
Date: Wed, 27 Feb 2013 16:38:29 -0500
Message-ID: <01fb01ce1532$c8a02690$59e073b0$@sympatico.ca> (raw)
In-Reply-To: <CAMkU=1zRqJ6Bn08KiN8vLBoVj14W5xXM7aJLgz+QZwLC0ZRbJQ@mail.gmail.com>
References: <005701ce0fb5$6f841f30$4e8c5d90$@sympatico.ca>
<001501ce1054$7b518380$71f48a80$@sympatico.ca>
<CAMkU=1yG884Nak4zPoNHwDGpC7w2nrKH6_xztPw4b7P9am0wdg@mail.gmail.com>
<B6F6FD62F2624C4C9916AC0175D56D8801B17F@jenmbs01.ad.intershop.net>
<006201ce1125$2c554150$84ffc3f0$@sympatico.ca>
<CAPmjWd1On_uAp6suqYoHuh3ksKD0xn6tOhOM_Z2vaweO-vexSA@mail.gmail.com>
<009001ce1137$f9c31bf0$ed4953d0$@sympatico.ca>
<CAMkU=1z-UOitt2mtnjn=XQ8kk2cL2nAnd2MfycLwju__qC49wQ@mail.gmail.com>
<010601ce137a$266b7f00$73427d00$@sympatico.ca>
<CAMkU=1wc1uMsx5viu6NHU=LFn3q_AjwUWE0y6vuCN=eYKZ+eAg@mail.gmail.com>
<01a601ce1482$07142db0$153c8910$@sympatico.ca>
<CAMkU=1zRqJ6Bn08KiN8vLBoVj14W5xXM7aJLgz+QZwLC0ZRbJQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
<< pgbouncer is more for making connections line up single-file when the
line is moving at a very fast clip, say 0.01 second per turn. If I were
trying to make tasks that can each last for hours or days line up and take
turns, I don't think pgbouncer would be the way to go.
>>
The recommendation at the time was assuming that write contention was
slowing things down and consuming resources, since I can't stop people from
creating big multi-threaded imports. Each import consists of about 50 writes
>> Is the original query you posted part of the transform process, rather
than being the production query you run after the ETL is over?
Neither, it is part of our auditing and maintenance processes. It is not
called with any great frequency. The audit report generates rows defining
how the a particular item (an "item" being a particular table/row) was
created: it returns the names of the import tables, the row ids, the write
operations and any transformation messages that may have been generated -
all in the order they occurred.
You can imagine how useful this in creating a document describing what
happened and why.
The same data generated by the report is used to "resurrect" an item. If -
for example - our business logic has changed, but the change only affects a
small sub-set of our core data, then we perform a "rollback" (a logical
cascading delete) on the affected items. Then we create a "rebuild" which is
a script that is generated to re-import ONLY the import table rows defined
in the audit report.
So, this query is not called often, but the fact is that if it takes over 30
seconds to load an item (because the audit report takes so long to prepare
the bitmap index scan when passed new query parameters) then it severely
restricts how much data we can resurrect at any one time.
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: stonec.register@sympatico.ca, jeff.janes@gmail.com
Subject: Re: Are bitmap index scans slow to start?
In-Reply-To: <01fb01ce1532$c8a02690$59e073b0$@sympatico.ca>
* 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