From: Robert Haas <robertmhaas@gmail.com>
To: Rainer Mager <rainer@vanten.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: difficulties with time based queries
Date: Mon, 6 Apr 2009 06:37:13 -0400
Message-ID: <603c8f070904060337u20dcb0d9h52612a2d4e93d2e5@mail.gmail.com> (raw)
In-Reply-To: <003601c9b668$bf63c2d0$3e2b4870$@com>
References: <003001c9b645$e8a7cec0$b9f76c40$@com>
<e7f9235d0904051646j7e8374afncf79e3708ae40c20@mail.gmail.com>
<003501c9b656$a9c73fe0$fd55bfa0$@com>
<7371.1238981597@sss.pgh.pa.us>
<003601c9b668$bf63c2d0$3e2b4870$@com>
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
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: robertmhaas@gmail.com, rainer@vanten.com
Subject: Re: difficulties with time based queries
In-Reply-To: <603c8f070904060337u20dcb0d9h52612a2d4e93d2e5@mail.gmail.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