From: Richard Neill <rn214@cam.ac.uk>
To: pgsql-performance@postgresql.org
Subject: Joint index including MAX() ?
Date: Sat, 09 Jan 2010 12:46:07 +0000
Message-ID: <4B487A8F.6000003@cam.ac.uk> (raw)
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
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: rn214@cam.ac.uk
Subject: Re: Joint index including MAX() ?
In-Reply-To: <4B487A8F.6000003@cam.ac.uk>
* 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