Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UB6xH-0003sf-M8 for pgsql-performance@arkaria.postgresql.org; Thu, 28 Feb 2013 17:07:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UB6xG-0000UW-DI for pgsql-performance@arkaria.postgresql.org; Thu, 28 Feb 2013 17:07:38 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8yuI-0008Q8-Ge for pgsql-performance@postgresql.org; Fri, 22 Feb 2013 20:07:47 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1U8yuE-0006zD-9t for pgsql-performance@postgresql.org; Fri, 22 Feb 2013 20:07:44 +0000 Received: from [192.168.1.65] (122-57-177-3.jetstream.xtra.co.nz [122.57.177.3]) (authenticated bits=0) by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id r1MK7Km8026212 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Sat, 23 Feb 2013 09:07:21 +1300 Message-ID: <5127CFF8.4010402@archidevsys.co.nz> Date: Sat, 23 Feb 2013 09:07:20 +1300 From: Gavin Flower Organization: ArchiDevSys User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:17.0) Gecko/20130110 Thunderbird/17.0.2 MIME-Version: 1.0 To: Nikolas Everett CC: Carlo Stonebanks , Marc Mamin , Jeff Janes , "pgsql-performance@postgresql.org" Subject: Re: Are bitmap index scans slow to start? References: <005701ce0fb5$6f841f30$4e8c5d90$@sympatico.ca> <001501ce1054$7b518380$71f48a80$@sympatico.ca> <006201ce1125$2c554150$84ffc3f0$@sympatico.ca> In-Reply-To: Content-Type: multipart/alternative; boundary="------------040702070904040103040602" 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 multi-part message in MIME format. --------------040702070904040103040602 Content-Type: text/plain; charset=windows-1252; format=flowed Content-Transfer-Encoding: quoted-printable X-MIME-Autoconverted: from 8bit to quoted-printable by mbx.knossos.net.nz id r1MK7Km8026212 On 23/02/13 08:05, Nikolas Everett wrote: > I can't really help, but I can make it more clear why postgres is=20 > choosing a _bitmap_ index scan rather than a regular index scan. With=20 > a regular index scan it pumps the index for the locations of the rows=20 > that it points to and loads those rows as it finds them. This works=20 > great if the rows in the index are sorta sorted - that way it isn't=20 > jumping around the table randomly. Random io is slow. In a bitmap=20 > index scan pg pumps the index and buffers the by shoving them in a big=20 > bitmap. Then, it walks the bitmap in order to produce in order io.=20 > PG makes the choice based on a measure of the index's correlation. > > The problem comes down to you inserting the sessions concurrently with=20 > one another. My instinct would be to lower the FILLFACTOR on newly=20 > created indecies so they can keep their entries more in order. I'm=20 > not sure why I have that instinct but it feels right. Also, you might=20 > could try clustering newly created tables on session_id and setting=20 > the fillfactor down so rows with the same session id will stick=20 > together on disk. > > Now that I look stuff up on the internet I'm not sure where I saw that=20 > pg tries to maintain a cluster using empty space from FILLFACTOR but I=20 > _think_ it does. I'm not sure what is going on with my google foo toda= y. > > Nik > > > On Fri, Feb 22, 2013 at 12:50 PM, Carlo Stonebanks=20 > >=20 > wrote: > > A cool idea, but if I understand it correctly very specific and > fussy. New DB=92s are spawned on this model, and all the developers > would have to be aware of this non-standard behaviour, and DBA=94s > 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 =96 like I said =96 an interesting idea for mor= e > 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 page= s 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 > > Could you use CLUSTER on the table after it had been closed off? If=20 appropriate, that should make the queries run much faster, as elated=20 entries will be inthe same or nearby blocks on disk. --------------040702070904040103040602 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable X-MIME-Autoconverted: from 8bit to quoted-printable by mbx.knossos.net.nz id r1MK7Km8026212
On 23/02/13 08:05, Nikolas Everett wrote:
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. =A0With 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. =A0This works great if the rows in the index are sorta sorted - that way it isn't jumping around the table randomly. =A0Random io is slow. =A0In a bitmap index sc= an pg pumps the index and buffers the by shoving them in a big bitmap. =A0Then, it walks the bitmap in order to produce in order io. =A0PG 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. =A0My instinct would be to lower the FILLFACTOR on newly created indecies so they can keep their entries more in order. =A0I'm not sure why I have that instinct but it feels right. =A0Also, 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. =A0I'm not sure what is going on with my google foo today.

Nik


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

A cool idea, but if I understand it correctly very specific and fussy. New DB=92s are spawned on this model, and all the developers would have to be aware of this non-standard behaviour, and DBA=94s would hav= e 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 =96 like I said =96 an interesting idea = for more specific challenges.

=A0

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?

=A0

=A0

>R= ebuilding the index might help, as it would put all the leaf pages holding values for session_id=3D27 adjacent to each other, s= o they would read from disk faster.=A0 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.=A0


=A0

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

Hello,<= br>
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


Could you use CLUSTER on the table after it had bee= n closed off?=A0 If appropriate, that should make the queries run muc= h faster, as elated entries will be in the same or nearby blocks on disk.
--------------040702070904040103040602--