On Sun, Apr 5, 2009 at 11:35 PM, Rainer Mager <rainer@vanten.com> wrote:
>> -----Original Message-----
>> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> "Rainer Mager" <rainer@vanten.com> writes:
>> >> 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?
>>
>> 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.
Even if your query "could use" an index four fields, a lot of times it
won't be the winning strategy, because it means reading a lot more
data from the disk. Plus, all of these huge indices are competing for
RAM with data from the table itself. You might want to think about
getting rid of all of the indices with more than 1 or 2 columns.
ad_log_unique is probably huge and it seems like it's probably not
improving your data integrity as much as you might think...
...Robert