To: Richard Neill <rn214@cam.ac.uk>
cc: pgsql-performance@postgresql.org
Subject: Re: Joint index including MAX() ? 
In-reply-to: <4B487A8F.6000003@cam.ac.uk> 
References: <4B487A8F.6000003@cam.ac.uk>
Comments: In-reply-to Richard Neill <rn214@cam.ac.uk>
	message dated "Sat, 09 Jan 2010 12:46:07 +0000"
Date: Sat, 09 Jan 2010 12:07:12 -0500
Message-ID: <8143.1263056832@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Richard Neill <rn214@cam.ac.uk> writes:
> I'm trying to optimise the speed of some selects with the where condition:
> WHERE id =
>   (SELECT MAX(id) FROM tbl_sort_report WHERE parcel_id_code='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);

You've got the index column order backwards: to make this query fast,
it has to be on (parcel_id_code, id).  The reason should be apparent
if you think about the index ordering.  With the correct index, the
backend can descend the btree looking for the last entry with
parcel_id_code='43024', and when it hits it, that's the max id.
The other way round, the best available strategy using the index
is to search backwards from the end (highest id) hoping to hit a
row with parcel_id_code='43024'.  That could take a long time.
Frequently the planner will think it's so slow that it shouldn't
even bother with the index, just seqscan.

			regards, tom lane