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: Fri, 22 Feb 2013 12:50:59 -0500
Message-ID: <006701ce1125$2c7ced90$8576c8b0$@sympatico.ca> (raw)
In-Reply-To: <CAMkU=1yG884Nak4zPoNHwDGpC7w2nrKH6_xztPw4b7P9am0wdg@mail.gmail.com>
References: <005701ce0fb5$6f841f30$4e8c5d90$@sympatico.ca>
<001501ce1054$7b518380$71f48a80$@sympatico.ca>
<CAMkU=1yG884Nak4zPoNHwDGpC7w2nrKH6_xztPw4b7P9am0wdg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi Jeff, thanks for the reply.
<<
What is going on during the interregnum? Whatever it is, it seems to be
driving the log_2013_01_session_idx index out of the cache, but not the
log_2013_01 table. (Or perhaps the table visit is getting the benefit of
effective_io_concurrency?)
.
Rebuilding the index might help, as it would put all the leaf pages holding
values for session_id=27 adjacent to each other, so they would read from
disk faster. But with a name like "session_id", I don't know how long such
clustering would last though.
>>
Technically, nothing should be happening. We used to keep one massive audit
log, and was impossible to manage due to its size. We then changed to a
strategy where every month a new audit log would be spawned, and since
log_2013_01 represents January, the log should be closed and nothing should
have changed (it is technically possible that a long-running process would
spill over into February, but not by this much). So, assuming that it's
stable, it should be a very good candidate for reindexing, no?
Our effective_io_concurrency is 1, and last I heard the PG host was a LINUX
4 drive RAID10, so I don't know if there is any benefit to raising this
number - and if there was any benfit, it would be to the Bitmap Scan, and
the problem is the data building before the fact.
>> the bitmap itself doesn't get cached. But the data needed to construct
the bitmap does get cached. It gets cached by the generic caching methods
of PG and the OS, not through something specific to bitmaps.
<<
This has always been a problem for me. I spend hours trying different
strategies and think I've solved the problem, when in fact it seems like a
cache has spun up, and then something else expires it and the problem is
back. Is there a way around this problem, can I force the expiration of a
cache?
Carlo
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: <006701ce1125$2c7ced90$8576c8b0$@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