Re: Joint index including MAX() ? - Mailing list pgsql-performance

From Tom Lane
Subject Re: Joint index including MAX() ?
Date
Msg-id 8143.1263056832@sss.pgh.pa.us
Whole thread Raw
In response to Joint index including MAX() ?  (Richard Neill <rn214@cam.ac.uk>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Dmitri Girski
Date:
Subject: Re: pg_connect takes 3.0 seconds
Next
From: Nickolay
Date:
Subject: Re: PG optimization question