From: Lefteris <lsidir@gmail.com>
To: Richard Neill <rn214@cam.ac.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Joint index including MAX() ?
Date: Sat, 9 Jan 2010 13:52:31 +0100
Message-ID: <852badbc1001090452w6c5295bve1cee840745bbba5@mail.gmail.com> (raw)
In-Reply-To: <4B487A8F.6000003@cam.ac.uk>
References: <4B487A8F.6000003@cam.ac.uk>
Hi,
I first suggestion would be to either build the index only on
parcel_id_code or on (parcel_id_code, id).
But I am not sure because I am new in pg:)
cheers,
lefteris
On Sat, Jan 9, 2010 at 1:46 PM, Richard Neill <rn214@cam.ac.uk> wrote:
> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
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: lsidir@gmail.com, rn214@cam.ac.uk
Subject: Re: Joint index including MAX() ?
In-Reply-To: <852badbc1001090452w6c5295bve1cee840745bbba5@mail.gmail.com>
* 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