Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8wmQ-0004m7-Te for pgsql-performance@arkaria.postgresql.org; Fri, 22 Feb 2013 17:51:31 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1U8wmP-0001wp-S4 for pgsql-performance@arkaria.postgresql.org; Fri, 22 Feb 2013 17:51:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8wmA-0001iz-EE for pgsql-performance@postgresql.org; Fri, 22 Feb 2013 17:51:14 +0000 Received: from toroondcbmts04.bellnexxia.net ([207.236.237.38] helo=toroondcbmts04-srv.bellnexxia.net) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8wm3-0001wk-6z for pgsql-performance@postgresql.org; Fri, 22 Feb 2013 17:51:14 +0000 Received: from toip58-bus.srvr.bell.ca ([67.69.240.185]) by toroondcbmts04-srv.bellnexxia.net (InterMail vM.8.00.01.00 201-2244-105-20090324) with ESMTP id <20130222175104.EBKK25653.toroondcbmts04-srv.bellnexxia.net@toip58-bus.srvr.bell.ca>; Fri, 22 Feb 2013 12:51:04 -0500 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: Ag0FAMarJ1FGMVM9/2dsb2JhbABDAYJDgyK7QIEKFnOCHwEBBQgCHgVMDQMCBgMNAQMEAQEaAgwHGS0JCAIEARILBYgCtwCIR48OBgEnAYMYA4Ehh0eFKo4/ikyDJQ X-IronPort-AV: E=Sophos;i="4.84,717,1355115600"; d="scan'208,217";a="15535645" 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: "'Marc Mamin'" , "'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: <006201ce1125$2c554150$84ffc3f0$@sympatico.ca> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0063_01CE10FB.437FD590" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQHaXprYpEPEIaXdLmMZOCaAwfiQhwMAB23aAo8fOP4CRcCoxZgu5Ocw 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_0063_01CE10FB.437FD590 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit A cool idea, but if I understand it correctly very specific and fussy. New DB's are spawned on this model, and all the developers would have to be aware of this non-standard behaviour, and DBA"s would have to create these indexes every month, for every DB (as the log tables are created every month). There are 89 session_id values in the January log (log_2013_01) so this would quickly get out of control. But - like I said - an interesting idea for more specific challenges. From: Marc Mamin [mailto:M.Mamin@intershop.de] Sent: February 21, 2013 2:41 PM To: Jeff Janes; Carlo Stonebanks Cc: pgsql-performance@postgresql.org Subject: AW: [PERFORM] Are bitmap index scans slow to start? >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. >If I'm right about the index disk-read time, then switching to a plain index scan rather than a bitmap index scan would make no difference--either way the data has to come off the disk. >>I'd prefer a >>strategy that allowed fast performance the first time, rather than slow the >>first time and extremely fast subsequently. Hello, if the index is only used to locate rows for single session_id, you may consider split it in a set of partial indexes. e.g. create index i_0 on foo where session_id%4 =0; create index i_1 on foo where session_id%4 =1; create index i_2 on foo where session_id%4 =2; create index i_3 on foo where session_id%4 =3; (can be built in parallel using separate threads) Then you will have to ensure that all your WHERE clauses also contain the index condition: WHERE session_id = 27 AND session_id%4 =27%4 regards, Marc Mamin ------=_NextPart_000_0063_01CE10FB.437FD590 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

A cool idea, but if I understand it correctly very specific and = fussy. New DB’s are spawned on this model, and all the developers = would have to be aware of this non-standard behaviour, and DBA”s = would have to create these indexes every month, for every DB (as the log = tables are created every month). There are 89 session_id values in the = January log (log_2013_01) so this would quickly get out of control. But = – like I said – an interesting idea for more specific = challenges.

 

From:= Marc Mamin = [mailto:M.Mamin@intershop.de]
Sent: February 21, 2013 2:41 = PM
To: Jeff Janes; Carlo Stonebanks
Cc: = pgsql-performance@postgresql.org
Subject: AW: [PERFORM] Are = bitmap index scans slow to start?

 

=  

>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.

>If I'm right = about the index disk-read time, then switching to a plain index scan = rather than a bitmap index scan would make no difference--either way the = data has to come off the disk.  =


 

>>I'd = prefer a
>>strategy that allowed fast performance the first = time, rather than slow the
>>first time and extremely fast = subsequently.

Hello,

if the index = is only used to locate rows for single session_id, you may consider = split it in a set of partial indexes.

e.g.
create index i_0 = on foo where session_id%4 =3D0;
create index i_1 on foo where = session_id%4 =3D1;
create index i_2 on foo where session_id%4 = =3D2;
create index i_3 on foo where session_id%4 =3D3;

(can be = built in parallel using separate threads)

Then you will have to = ensure that all your WHERE clauses also contain the index = condition:

WHERE session_id =3D 27 AND session_id%4 = =3D27%4

regards,

Marc = Mamin

------=_NextPart_000_0063_01CE10FB.437FD590--