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=wsXCSxNIeNU34RMWmp3zfp7iqjPJwU3+ofBPSMjPm5uFHswRImTaPt3xd0EDrKh69R
	uhS42xDGBi6rejjoA7VqRNN83fXP1tnvlVRCOyMXB+mm1JWDVllOgwcnWCGYLg6w4tYQ
	s6NDMUkKvo89ZPYRfPKAxcluP/8AtJQyY7YvE=
MIME-Version: 1.0
In-Reply-To: <49D0E687.50605@megafon.hr>
References: <49D0D237.7020308@megafon.hr> <3028.1238422571@sss.pgh.pa.us>
	<49D0E687.50605@megafon.hr>
Date: Mon, 30 Mar 2009 10:25:36 -0600
Message-ID: <dcc563d10903300925m88a73c7kf90084b5e088a445@mail.gmail.com>
Subject: Re: Forcing seq_scan off for large table joined with tiny
	table yeilds improved performance
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Mario Splivalo <mario.splivalo@megafon.hr>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Mon, Mar 30, 2009 at 9:34 AM, Mario Splivalo
<mario.splivalo@megafon.hr> wrote:

> =A0 =A0 =A0 =A0 -> =A0Bitmap Heap Scan on photo_info_data u (cost=3D2193.=
50..26798.74
> rows=3D109024 width=3D9) (actual time=3D0.025..0.030 rows=3D3 loops=3D2)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 Recheck Cond: ((u.field_name)::text =3D (t.ke=
y)::text)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Bitmap Index Scan on photo_info_data_ix=
__field_name
> =A0(cost=3D0.00..2166.24 rows=3D109024 width=3D0) (actual time=3D0.019..0=
.019 rows=3D3
> loops=3D2)

> So, I guess I solved my problem! :) The explain analyze still shows that =
row
> estimate is 'quite off' (109024 estimated vs only 3 actuall), but the que=
ry
> is light-speeded :)

It's not really solved, it's just a happy coincidence that the current
plan runs well.  In order to keep the query planner making good
choices you need to increase stats target for the field in the index
above.  The easiest way to do so is to do this:

alter database mydb set default_statistics_target=3D100;

and run analyze again:

analyze;

> I tought that having primary key (and auto-index because of primary key) =
on
> (photo_id, field_name) should be enough. Now I have two indexes on
> field_name, but that seems to do good.

Nope, it's about the stats collected that let the planner make the right ch=
oice.