Message-ID: <4B487A8F.6000003@cam.ac.uk>
Date: Sat, 09 Jan 2010 12:46:07 +0000
From: Richard Neill <rn214@cam.ac.uk>
Organization: RUO3.org
User-Agent: Thunderbird 2.0.0.22 (X11/20090608)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Joint index including MAX() ?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Dear All,

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);


So, my question is, is there any way to improve this? I'd expect that an 
index on   ( 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 
from 0...99999 and back again every few hours.

fsc_log=> \d tbl_sort_report
                                     Table "public.tbl_sort_report"
         Column        |           Type           | 
  Modifiers
----------------------+--------------------------+-----------------------------------------------------
  id                   | bigint                   | not null default 
nextval('master_id_seq'::regclass)
  timestamp            | timestamp with time zone |
  parcel_id_code       | integer                  |
(etc)


EXPLAIN ANALYZE (SELECT MAX(id) FROM tbl_sort_report WHERE 
parcel_id_code='43024');
 
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
  Result  (cost=7.34..7.35 rows=1 width=0) (actual time=17.712..17.714 
rows=1 loops=1)
    InitPlan 1 (returns $0)
      ->  Limit  (cost=0.00..7.34 rows=1 width=8) (actual 
time=17.705..17.705 rows=0 loops=1)
            ->  Index Scan Backward using testidx3 on tbl_sort_report 
(cost=0.00..14.67 rows=2 width=8) (actual time=17.700..17.700 rows=0 
loops=1)
                  Index Cond: (parcel_id_code = 43024)
                  Filter: (id IS NOT NULL)
  Total runtime: 17.786 ms