From: | "Rainer Mager" <rainer(at)vanten(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: difficulties with time based queries |
Date: | 2009-04-06 03:35:36 |
Message-ID: | 003601c9b668$bf63c2d0$3e2b4870$@com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> "Rainer Mager" <rainer(at)vanten(dot)com> writes:
> >> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)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?
>
> Since you've got 8.3 it's easy: select pg_relation_size('tablename')
> (or indexname). The result is in bytes, so you might want to
> divide by 1K or 1M to keep the number readable.
Ok, nice and simple...I like it:
The result for the table ad_log, is 30,063 MB. The result for the index,
ad_log_date_all, is 17,151 MB. I guess this roughly makes sense since the
index is on 4 fields and the table only has 6 fields.
For the particular query I'm trying to optimize at the moment I believe I
should be able to use an index that references only 2 fields, which, I
imagine, should reduce the time needed to read it. I'll play with this a bit
and see what happens.
Any other suggestions?
--Rainer
From | Date | Subject | |
---|---|---|---|
Next Message | roopasatish | 2009-04-06 06:54:33 | probelm with alter table add constraint...... |
Previous Message | Greg Sabino Mullane | 2009-04-06 02:11:21 | Re: Best replication solution? |