From: Rainer Mager <rainer@vanten.com>
To: pgsql-performance@postgresql.org
Subject: Re: difficulties with time based queries
Date: Mon, 6 Apr 2009 10:26:08 +0900
Message-ID: <003501c9b656$a9c73fe0$fd55bfa0$@com> (raw)
In-Reply-To: <e7f9235d0904051646j7e8374afncf79e3708ae40c20@mail.gmail.com>
References: <003001c9b645$e8a7cec0$b9f76c40$@com>
<e7f9235d0904051646j7e8374afncf79e3708ae40c20@mail.gmail.com>
Thanks for all the replies, I'll try to address the follow up questions:
> From: David Wilson [mailto:david.t.wilson@gmail.com]
>
> The stats look good and it's using a viable index for your query. What
> kind of hardware is this on, and what are the relevant postgresql.conf
> lines? (Or, for that matter, what does iostat say while this query's
> running?)
I'm running on Windows, so I don't have iostat, but perfmon tells me my Avg.
Disk Queue Length went up to 1.2 during the query (versus a normal value of
about 0.02). Also disk throughput was at about 1.2 MB/s during the query. I
don't know how much of this is random versus linear.
> From: PFC [mailto:lists@peufeu.com]
>
> With this quantity of rows, you want to try to make the disk
> accesses as
> linear as possible.
> This means your table should be organized on disk by date, at
> least
> roughly.
> If your data comes from an import that was sorted on some other
> column,
> this may not be the case.
>
> What kind of bytes/s do you get from the drives ?
The data should be mostly ordered by date. It is all logged in semi-realtime
such that 99% will be logged within an hour of the timestamp. Also, as
stated above, during this query it was about 1.2 MB/s, which I know isn't
great. I admit this isn't the best hardware in the world, but I would expect
better than that for linear queries.
> Do you UPDATE or DELETE a lot from this table ? Is it vacuum'd
> enough ?
No, this table has no UPDATEs or DELETEs. It is auto vacuum'd, but no manual
vacuuming.
In regards to clustering, I'm hesitant to do that unless I have no other
choice. My understanding is that I would need to do periodic re-clustering
to maintain it, and during that time the table is very busy.
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Hmm ... it's pretty unusual to see the index fetch portion of a bitmap
> scan take the bulk of the runtime. Usually that part is fast and where
> the pain comes is in fetching from the heap. I wonder whether that
> index has become bloated. How big are the table and the index
> physically? (Look at pg_class.relpages, or if you want a really
> accurate number try pg_relation_size().)
Can you give me some more info on how to look at these stats? That is,
what/where is pg_class.relpages, etc. I'll also do some searching for this
info.
> What Postgres version is this, exactly?
8.3.3
> BTW, I think you've gone way overboard in your indexing of this table;
> those indexes are certainly consuming well more space than the table
> does, and a lot of them are redundant.
Agreed, I need to look carefully at all of the queries we do on this table
and reduce this.
--Rainer
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: rainer@vanten.com
Subject: Re: difficulties with time based queries
In-Reply-To: <003501c9b656$a9c73fe0$fd55bfa0$@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