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=ZMsAeAbzuo8MXusiVrru0NCdwlEd1jPp9xSk0ijenKI4jK3C00y3AvOENw433Eau3A
	Ob5+fBQjAl74nd+xyU5Vv7cJHSSIIpelxP9QMOC3OxXcpKnfZS0/qptA+BjwErBJdj11
	v8Nv8F1fsgGYtcjTE2SyJI9vCGJzHW0dEYvvQ=
MIME-Version: 1.0
In-Reply-To: <4B487A8F.6000003@cam.ac.uk>
References: <4B487A8F.6000003@cam.ac.uk>
Date: Sat, 9 Jan 2010 13:52:31 +0100
Message-ID: <852badbc1001090452w6c5295bve1cee840745bbba5@mail.gmail.com>
Subject: Re: Joint index including MAX() ?
From: Lefteris <lsidir@gmail.com>
To: Richard Neill <rn214@cam.ac.uk>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,

I first suggestion would be to either build the index only on
parcel_id_code or on (parcel_id_code, id).

But I am not sure because I am new in pg:)

cheers,
lefteris

On Sat, Jan 9, 2010 at 1:46 PM, Richard Neill <rn214@cam.ac.uk> wrote:
> Dear All,
>
> I'm trying to optimise the speed of some selects with the where condition=
:
>
> WHERE id =3D
> =A0(SELECT MAX(id) FROM tbl_sort_report WHERE parcel_id_code=3D'43024')
>
>
> This is relatively slow, taking about 15-20ms, even though I have a joint
> index on both fields:
>
> CREATE INDEX testidx3 ON tbl_sort_report (id, parcel_id_code);
>
>
> So, my question is, is there any way to improve this? I'd expect that an
> index on =A0 ( max(id),parcel_id_code ) would be ideal, excepting that
> postgres won't allow that (and such an index probably doesn't make much
> conceptual sense).
>
>
> Explain Analyze is below.
>
> Thanks,
>
> Richard
>
>
>
> Here is part of the schema. id is the primary key; parcel_id_code loops f=
rom
> 0...99999 and back again every few hours.
>
> fsc_log=3D> \d tbl_sort_report
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Ta=
ble "public.tbl_sort_report"
> =A0 =A0 =A0 =A0Column =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 Type =A0 =A0 =
=A0 =A0 =A0 | =A0Modifiers
> ----------------------+--------------------------+-----------------------=
------------------------------
> =A0id =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | bigint =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 | not null default
> nextval('master_id_seq'::regclass)
> =A0timestamp =A0 =A0 =A0 =A0 =A0 =A0| timestamp with time zone |
> =A0parcel_id_code =A0 =A0 =A0 | integer =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0|
> (etc)
>
>
>
>
> EXPLAIN ANALYZE (SELECT MAX(id) FROM tbl_sort_report WHERE
> parcel_id_code=3D'43024');
>
> QUERY PLAN
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------
> =A0Result =A0(cost=3D7.34..7.35 rows=3D1 width=3D0) (actual time=3D17.712=
..17.714 rows=3D1
> loops=3D1)
> =A0 InitPlan 1 (returns $0)
> =A0 =A0 -> =A0Limit =A0(cost=3D0.00..7.34 rows=3D1 width=3D8) (actual tim=
e=3D17.705..17.705
> rows=3D0 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan Backward using testidx3 on tbl_sort_=
report
> (cost=3D0.00..14.67 rows=3D2 width=3D8) (actual time=3D17.700..17.700 row=
s=3D0
> loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: (parcel_id_code =3D 43024)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Filter: (id IS NOT NULL)
> =A0Total runtime: 17.786 ms
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org=
)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>