DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=EfTzFO7tmhZnLc2HWnZgNS5whnU/6/NmqlQ1Q3G5gKg0k7Beng1GY9fDV60Whhz2iV
	3cqPLmuwLvJ2nFR1DD0JbxhNivC/NjAp+8V+nzePWac97El0X7Sv26H/okBhLkLVRagg
	Ik4X2QruEJea8xK//t3gzStraUAyaWgfLApRw=
MIME-Version: 1.0
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>
Date: Mon, 6 Apr 2009 06:37:13 -0400
Message-ID: <603c8f070904060337u20dcb0d9h52612a2d4e93d2e5@mail.gmail.com>
Subject: Re: difficulties with time based queries
From: Robert Haas <robertmhaas@gmail.com>
To: Rainer Mager <rainer@vanten.com>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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. =A0Usually that part is fast and
>> where
>> >> the pain comes is in fetching from the heap. =A0 I wonder whether tha=
t
>> >> index has become bloated. =A0How big are the table and the index
>> >> physically? =A0(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). =A0The 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