From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Neill <rn214@cam.ac.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Joint index including MAX() ?
Date: Sat, 09 Jan 2010 12:07:12 -0500
Message-ID: <8143.1263056832@sss.pgh.pa.us> (raw)
In-Reply-To: <4B487A8F.6000003@cam.ac.uk>
References: <4B487A8F.6000003@cam.ac.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, rn214@cam.ac.uk
Subject: Re: Joint index including MAX() ?
In-Reply-To: <8143.1263056832@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox