From: Mark Liberman <mliberman@mixedsignals.com>
To: pgsql-performance@postgresql.org
Subject: Re: Help in avoiding a query 'Warm-Up' period/shared buffer cache
Date: Thu, 5 Jan 2006 18:15:36 -0800
Message-ID: <200601051815.36181.mliberman@mixedsignals.com> (raw)
In-Reply-To: <dpk922$slo$1@news.hub.org>
References: <200601041449.43557.mliberman@mixedsignals.com>
<dpk922$slo$1@news.hub.org>
On Thursday 05 January 2006 15:12, Qingqing Zhou wrote:
> "Mark Liberman" <mliberman@mixedsignals.com> wrote
>
> > First run, after a night of inactivity:
> >
> > -> Bitmap Index Scan on
> > 1min_events_file_id_begin_idx (cost=0.00..37.85 rows=3670 width=0)
> > (actual time=313.468..313.468 rows=11082
> > loops=1)
> > Index Cond: (file_id = 137271)
> > Total runtime: 313.643 ms
> >
> > Second run, after that:
> >
> > -> Bitmap Index Scan on
> > 1min_events_file_id_begin_idx (cost=0.00..37.85 rows=3670 width=0)
> > (actual time=2.106..2.106 rows=11082 loops=1)
> > Index Cond: (file_id = 137271)
> > Total runtime: 2.276 ms
>
> It is clear that the first query takes longer time because of the IO time
> of index 1min_events_file_id_begin_idx (see 313.468 vs. 2.106). I am afraid
> currently there is no easy solution for this situation, unless you could
> predicate which part of relation/index your query will use, then you can
> preload or "warm-up" cache for it.
>
> Regards,
> Qingqing
Thanks Qingqing,
this actually helped me determine that the compound index,
1min_events_file_id_begin_idx, is not the proper index to use as it is based
on file_id and begin_time - the later of which is not involved in the where
clause. It is only using that index to "filter" out the listed file_id.
Now, my follow-up question / assumption. I am assuming that the IO time is
so long on that index because it has to read the entire index (for that
file_id) into memory (because it cannot just scan the rows with a certain
date range because we are not using begin_time in the where clause).
But, if I replaced that compound index with the proper compound index of
file_id / end_time, it would give similar performance results to the scan on
1min_events_end_idx (which was < 1 ms). E.g. the latest rows that were
updated are more likely to be in the cache - and it is smart enough to only
read the index rows that it needs.
Alternatively, I could create a single index on file_id (and rely upon the new
bitmap scan capabilities in 1.2). But, I fear that, although this will be
smaller than the erroneous compound index on file_id / begin_time, it will
still display the same behavior in that it will need to read all rows from
that index for the appropriate file_id - and since the data goes back every
minute for 60 days, that IO might be large.
Obviously, I will be testing this - but it might take a few days, as I haven't
figure out how to simulate the "period of inactivity" to get the data flushed
out of the cache ... so I have to run this each morning. But, any
confirmation / corrections to my assumptions are greatly appreciated. E.g. is
the compound index the way to go, or the solo index on file_id?
Thanks,
Mark
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: mliberman@mixedsignals.com
Subject: Re: Help in avoiding a query 'Warm-Up' period/shared buffer cache
In-Reply-To: <200601051815.36181.mliberman@mixedsignals.com>
* 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