From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: 'Marc Mamin' <M.Mamin@intershop.de>
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: <006201ce1125$2c554150$84ffc3f0$@sympatico.ca> (raw)
In-Reply-To: <B6F6FD62F2624C4C9916AC0175D56D8801B17F@jenmbs01.ad.intershop.net>
References: <005701ce0fb5$6f841f30$4e8c5d90$@sympatico.ca>
<001501ce1054$7b518380$71f48a80$@sympatico.ca>
<CAMkU=1yG884Nak4zPoNHwDGpC7w2nrKH6_xztPw4b7P9am0wdg@mail.gmail.com>
<B6F6FD62F2624C4C9916AC0175D56D8801B17F@jenmbs01.ad.intershop.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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, M.Mamin@intershop.de, jeff.janes@gmail.com
Subject: Re: Are bitmap index scans slow to start?
In-Reply-To: <006201ce1125$2c554150$84ffc3f0$@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