Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8ysW-0005JC-PA for pgsql-performance@arkaria.postgresql.org; Fri, 22 Feb 2013 20:05:57 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1U8ysW-0007S5-9U for pgsql-performance@arkaria.postgresql.org; Fri, 22 Feb 2013 20:05:56 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8ysV-0007S0-0K for pgsql-performance@postgresql.org; Fri, 22 Feb 2013 20:05:55 +0000 Received: from toroondcbmts08.bellnexxia.net ([207.236.237.42] helo=toroondcbmts08-srv.bellnexxia.net) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8ysP-0006yU-8W for pgsql-performance@postgresql.org; Fri, 22 Feb 2013 20:05:53 +0000 Received: from toip53-bus.srvr.bell.ca ([67.69.240.54]) by toroondcbmts08-srv.bellnexxia.net (InterMail vM.8.00.01.00 201-2244-105-20090324) with ESMTP id <20130222200548.IDDS9022.toroondcbmts08-srv.bellnexxia.net@toip53-bus.srvr.bell.ca>; Fri, 22 Feb 2013 15:05:48 -0500 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: Ag0FALPMJ1FGMVM9/2dsb2JhbAA8BwGCQ4Miu0OBDRZzgh8BAQQBCAIdAQVMDQMCBgMNAQMEAQEaAgwHGQglCQgCBBMLBYdwAwkGtV4NgQeIPYw3gRKBRQYBJwGDGAOBIYdHhSqEZ4FjAYd0hTWFF4Ml X-IronPort-AV: E=Sophos;i="4.84,718,1355115600"; d="scan'208,217";a="405820593" Received: from unknown (HELO CAPRICA) ([70.49.83.61]) by toip53-bus.srvr.bell.ca with ESMTP; 22 Feb 2013 15:05:38 -0500 From: "Carlo Stonebanks" To: "'Nikolas Everett'" Cc: "'Marc Mamin'" , "'Jeff Janes'" , References: <005701ce0fb5$6f841f30$4e8c5d90$@sympatico.ca> <001501ce1054$7b518380$71f48a80$@sympatico.ca> <006201ce1125$2c554150$84ffc3f0$@sympatico.ca> In-Reply-To: Subject: Re: Are bitmap index scans slow to start? Date: Fri, 22 Feb 2013 15:05:34 -0500 Message-ID: <009001ce1137$f9c31bf0$ed4953d0$@sympatico.ca> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0091_01CE110E.10EE4C70" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQHaXprYpEPEIaXdLmMZOCaAwfiQhwMAB23aAo8fOP4CRcCoxQGZrFXnAfFz5MCYEqx6oA== 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_0091_01CE110E.10EE4C70 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit >> Also, you might could try clustering newly created tables on session_id and setting the fillfactor down so rows with the same session id will stick together on disk. << My understanding of PG's cluster is that this is a one-time command that creates a re-ordered table and doesn't maintain the clustered order until the command is issued again. During the CLUSTER, the table is read and write locked. So, in order for me to use this I would need to set up a timed event to CLUSTER occasionally. >> I can't really help, but I can make it more clear why postgres is choosing a _bitmap_ index scan rather than a regular index scan << The EXPLAIN ANALYZE is showing it is taking a long time to prepare the bitmap (i.e.-> Bitmap Index Scan on log_2013_01_session_idx (cost=0.00..63186.52 rows=2947664 width=0) (actual time=32611.918..32611.918 rows=2772042 loops=1)" Index Cond: (session_id = 27)" the bitmap scan is actually very fast. Jeff sasys that the bitmap is not cached, so I will assume the PG general caches being created are of general use. I think what I need to do is figure out is: 1) Why does it take 36 seconds to set up the general index caches? 2) What can I do about it (what stats do I need to look at)? 3) How can I force these caches to expire so I can tell if the strategy worked? From: Nikolas Everett [mailto:nik9000@gmail.com] Sent: February 22, 2013 2:05 PM To: Carlo Stonebanks Cc: Marc Mamin; Jeff Janes; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Are bitmap index scans slow to start? I can't really help, but I can make it more clear why postgres is choosing a _bitmap_ index scan rather than a regular index scan. With a regular index scan it pumps the index for the locations of the rows that it points to and loads those rows as it finds them. This works great if the rows in the index are sorta sorted - that way it isn't jumping around the table randomly. Random io is slow. In a bitmap index scan pg pumps the index and buffers the by shoving them in a big bitmap. Then, it walks the bitmap in order to produce in order io. PG makes the choice based on a measure of the index's correlation. The problem comes down to you inserting the sessions concurrently with one another. My instinct would be to lower the FILLFACTOR on newly created indecies so they can keep their entries more in order. I'm not sure why I have that instinct but it feels right. Also, you might could try clustering newly created tables on session_id and setting the fillfactor down so rows with the same session id will stick together on disk. Now that I look stuff up on the internet I'm not sure where I saw that pg tries to maintain a cluster using empty space from FILLFACTOR but I _think_ it does. I'm not sure what is going on with my google foo today. Nik On Fri, Feb 22, 2013 at 12:50 PM, Carlo Stonebanks wrote: 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_0091_01CE110E.10EE4C70 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

>> Also, you might could try clustering newly created = tables on session_id and setting the fillfactor down so rows with the = same session id will stick together on disk.

<< 

 

My understanding of PG’s cluster is that this is a one-time = command that creates a re-ordered table and doesn’t maintain the = clustered order until the command is issued again. During the CLUSTER, = the table is read and write locked. So, in order for me to use this I = would need to set up a timed event to CLUSTER = occasionally.

 

>> I can't really help, but I can make it more clear why = postgres is choosing a _bitmap_ index scan rather than a regular index = scan

<< 

 

The EXPLAIN ANALYZE is showing it is taking a = long time to prepare the bitmap (i.e.->  Bitmap Index = Scan on log_2013_01_session_idx  = (cost=3D0.00..63186.52

rows=3D2947664 width=3D0) (actual = time=3D32611.918..32611.918 rows=3D2772042 loops=3D1)" Index Cond: = (session_id =3D 27)" the bitmap scan = is actually very fast. Jeff sasys that the bitmap is not cached, so I = will assume the PG general caches being created are of general use. =

 

I think what I need = to do is figure out is:

 

1)      = Why does it = take 36 seconds to set up the general index = caches?

2)      = What can I = do about it (what stats do I need to look at)?

3)      = How can I = force these caches to expire so I can tell if the strategy = worked?

 

 

 

 

From:= Nikolas = Everett [mailto:nik9000@gmail.com]
Sent: February 22, 2013 = 2:05 PM
To: Carlo Stonebanks
Cc: Marc Mamin; Jeff = Janes; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] = Are bitmap index scans slow to start?

 

I can't = really help, but I can make it more clear why postgres is choosing a = _bitmap_ index scan rather than a regular index scan.  With a = regular index scan it pumps the index for the locations of the rows that = it points to and loads those rows as it finds them.  This works = great if the rows in the index are sorta sorted - that way it isn't = jumping around the table randomly.  Random io is slow.  In a = bitmap index scan pg pumps the index and buffers the by shoving them in = a big bitmap.  Then, it walks the bitmap in order to produce in = order io.  PG makes the choice based on a measure of the index's = correlation.

 

The problem comes down to you inserting the sessions = concurrently with one another.  My instinct would be to lower the = FILLFACTOR on newly created indecies so they can keep their entries more = in order.  I'm not sure why I have that instinct but it feels = right.  Also, you might could try clustering newly created tables = on session_id and setting the fillfactor down so rows with the same = session id will stick together on disk.

 

Now that I look stuff up on the internet I'm not sure = where I saw that pg tries to maintain a cluster using empty space from = FILLFACTOR but I _think_ it does.  I'm not sure what is going on = with my google foo today.

 

Nik

 

On Fri, Feb 22, 2013 at 12:50 PM, Carlo Stonebanks = <stonec.register@sympatico.ca> = wrote:

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?

 <= /o:p>

 

>Rebuildi= ng 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_0091_01CE110E.10EE4C70--