Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8wmA-0004lD-Pd for pgsql-performance@arkaria.postgresql.org; Fri, 22 Feb 2013 17:51:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1U8wmA-0001ik-6b for pgsql-performance@arkaria.postgresql.org; Fri, 22 Feb 2013 17:51:14 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8wm7-0001fj-OZ for pgsql-performance@postgresql.org; Fri, 22 Feb 2013 17:51:12 +0000 Received: from toroondcbmts05-srv.bellnexxia.net ([207.236.237.39]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8wm4-0004md-Hn for pgsql-performance@postgresql.org; Fri, 22 Feb 2013 17:51:10 +0000 Received: from toip58-bus.srvr.bell.ca ([67.69.240.185]) by toroondcbmts05-srv.bellnexxia.net (InterMail vM.8.00.01.00 201-2244-105-20090324) with ESMTP id <20130222175105.ENHT26520.toroondcbmts05-srv.bellnexxia.net@toip58-bus.srvr.bell.ca>; Fri, 22 Feb 2013 12:51:05 -0500 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AgsFAMarJ1FGMVM9/2dsb2JhbABEgkODIrtAgQoWc4IfAQEEAQgCHgVMDQMCBgMNORk+AgQeBYd8BrcAiEePDgeDQAOBIYdHhSqOP4pMgyU X-IronPort-AV: E=Sophos;i="4.84,717,1355115600"; d="scan'208,217";a="15535646" Received: from unknown (HELO CAPRICA) ([70.49.83.61]) by toip58-bus.srvr.bell.ca with ESMTP; 22 Feb 2013 12:51:03 -0500 From: "Carlo Stonebanks" To: "'Jeff Janes'" Cc: References: <005701ce0fb5$6f841f30$4e8c5d90$@sympatico.ca> <001501ce1054$7b518380$71f48a80$@sympatico.ca> In-Reply-To: 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> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0068_01CE10FB.43A781D0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQHaXprYpEPEIaXdLmMZOCaAwfiQhwMAB23aAo8fOP6YQQnW8A== Content-Language: en-ca X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_0068_01CE10FB.43A781D0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0068_01CE10FB.43A781D0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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=3D27 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

 

------=_NextPart_000_0068_01CE10FB.43A781D0--