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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
