pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedJoint index including MAX() ?
4+ messages / 4 participants
[nested] [flat]
* Joint index including MAX() ?
@ 2010-01-09 12:46 Richard Neill <rn214@cam.ac.uk>
0 siblings, 3 replies; 4+ messages in thread
From: Richard Neill @ 2010-01-09 12:46 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Joint index including MAX() ?
@ 2010-01-09 12:51 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Richard Neill <rn214@cam.ac.uk>
2 siblings, 0 replies; 4+ messages in thread
From: Grzegorz Jaśkiewicz @ 2010-01-09 12:51 UTC (permalink / raw)
To: Richard Neill <rn214@cam.ac.uk>; +Cc: pgsql-performance
you can also try :
select val FROM table ORDER BY val DESC LIMIT 1;
which usually is much quicker.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Joint index including MAX() ?
@ 2010-01-09 12:52 Lefteris <lsidir@gmail.com>
parent: Richard Neill <rn214@cam.ac.uk>
2 siblings, 0 replies; 4+ messages in thread
From: Lefteris @ 2010-01-09 12:52 UTC (permalink / raw)
To: Richard Neill <rn214@cam.ac.uk>; +Cc: pgsql-performance
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
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Joint index including MAX() ?
@ 2010-01-09 17:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Neill <rn214@cam.ac.uk>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2010-01-09 17:07 UTC (permalink / raw)
To: Richard Neill <rn214@cam.ac.uk>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-01-09 17:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-09 12:46 Joint index including MAX() ? Richard Neill <rn214@cam.ac.uk>
2010-01-09 12:51 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2010-01-09 12:52 ` Lefteris <lsidir@gmail.com>
2010-01-09 17:07 ` Tom Lane <tgl@sss.pgh.pa.us>
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