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=ugYXmX2vVb4hjKAYt5fzfhZPeDC8SZuAQzq2tLZvrrM3nlMnDiFGFM0nVhej14viiO
	OAXPQCZMiraOPGS3diM74tTTJP4NX8clwKv7iRsHUVm3i7TAZgxHASxj/mCuXD33WRaM
	CG7HJ7AQnLXKMLGVJDQ9HhF6xiopTc8baYm+I=
MIME-Version: 1.0
In-Reply-To: <49C3CF52.9090602@collab.net>
References: <49C2AC75.5060601@collab.net> <49C3600E.20504@archonet.com>
	<49C3CF52.9090602@collab.net>
Date: Fri, 20 Mar 2009 15:04:57 -0400
Message-ID: <603c8f070903201204s53f2a3cvbf9afc8649b2201a@mail.gmail.com>
Subject: Re: Need help with one query
From: Robert Haas <robertmhaas@gmail.com>
To: Anne Rosset <arosset@collab.net>
Cc: Richard Huxton <dev@archonet.com>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Fri, Mar 20, 2009 at 1:16 PM, Anne Rosset <arosset@collab.net> wrote:
> Richard Huxton wrote:
>> Anne Rosset wrote:
>>> EXPLAIN ANALYZE
>>> SELECT
>>> =A0audit_change.id =A0 =A0 =A0 =A0 =A0 =A0 AS id,
>>> =A0audit_change.audit_entry_id AS auditEntryId,
>>> =A0audit_entry.object_id =A0 =A0 =A0 AS objectId,
>>> =A0audit_change.property_name =A0AS propertyName,
>>> =A0audit_change.property_type =A0AS propertyType,
>>> =A0audit_change.old_value =A0 =A0 =A0AS oldValue,
>>> =A0audit_change.new_value =A0 =A0 =A0AS newValue,
>>> =A0audit_change.flexfield =A0 =A0 =A0AS flexField
>>> FROM
>>> =A0audit_entry audit_entry, audit_change audit_change
>>> WHERE
>>> =A0audit_change.audit_entry_id =3D audit_entry.id
>>> =A0AND audit_entry.object_id =3D 'artf414029';
>>>
>>
>> [query reformatted to make it more readable]
>>
>> Not quite clear why you are aliasing the tables to their own names...
>>
>>
>>>
>>>
>>> -----------------------------------------------------------------------=
----------------------------------------------------------------------
>>>
>>> Hash Join =A0(cost=3D8.79..253664.55 rows=3D4 width=3D136) (actual
>>> time=3D4612.674..6683.158 rows=3D4 loops=3D1)
>>> =A0Hash Cond: ((audit_change.audit_entry_id)::text =3D
>>> (audit_entry.id)::text)
>>> =A0-> =A0Seq Scan on audit_change =A0(cost=3D0.00..225212.52 rows=3D758=
4852
>>> width=3D123) (actual time=3D0.009..2838.216 rows=3D7584852 loops=3D1)
>>> =A0-> =A0Hash =A0(cost=3D8.75..8.75 rows=3D3 width=3D45) (actual time=
=3D0.049..0.049
>>> rows=3D4 loops=3D1)
>>> =A0 =A0 =A0 -> =A0Index Scan using audit_entry_object on audit_entry
>>> (cost=3D0.00..8.75 rows=3D3 width=3D45) (actual time=3D0.033..0.042 row=
s=3D4 loops=3D1)
>>> =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((object_id)::text =3D 'artf414029'=
::text)
>>> Total runtime: 6683.220 ms
>>>
>>
>> Very odd. It knows the table is large and that the seq-scan is going to
>> be expensive.
>>
>> Try issuing "set enable_seqscan =3D off" and run the explain analyse
>> again. That should show the cost of using the indexes.
>>
>>
>
> With "set enable_seqscan =3D off":
>
> QUERY PLAN
> -------------------------------------------------------------------------=
-----------------------------------------------------------------
> Nested Loop (cost=3D11.35..12497.53 rows=3D59 width=3D859) (actual
> time=3D46.074..49.742 rows=3D7 loops=3D1)
> -> Index Scan using audit_entry_pk on audit_entry (cost=3D0.00..7455.95
> rows=3D55 width=3D164) (actual time=3D45.940..49.541 rows=3D2 loops=3D1)
> Filter: ((object_id)::text =3D 'artf1024'::text)
> -> Bitmap Heap Scan on audit_change (cost=3D11.35..90.93 rows=3D59 width=
=3D777)
> (actual time=3D0.086..0.088 rows=3D4 loops=3D2)
> Recheck Cond: ((audit_change.audit_entry_id)::text =3D (audit_entry.id)::=
text)
> -> Bitmap Index Scan on audit_change_entry (cost=3D0.00..11.33 rows=3D59
> width=3D0) (actual time=3D0.076..0.076 rows=3D4 loops=3D2)
> Index Cond: ((audit_change.audit_entry_id)::text =3D (audit_entry.id)::te=
xt)
> Total runtime: 49.801 ms
>
>
> The db version is 8.2.4
>
> We are wondering if it is because of our audit_entry_id's format (like
> 'adte1DDFEA5B011C8988C3928752'). =A0Any inputs?
> Thanks,
> Anne

Something is wrong here.  How can setting enable_seqscan to off result
in a plan with a far lower estimated cost than the original plan?  If
the planner thought the non-seq-scan plan is cheaper, it would have
picked that one to begin with.

...Robert