pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp optimise this ?
4+ messages / 3 participants
[nested] [flat]
* help optimise this ?
@ 2002-11-21 14:22 Peter Galbavy <peter.galbavy@knowtion.net>
0 siblings, 1 reply; 4+ messages in thread
From: Peter Galbavy @ 2002-11-21 14:22 UTC (permalink / raw)
To: pgsql-sql
I have a table of image 'instances' where the columns include:
md5 char(32), -- the 'original' image md5 key
file_md5 char(32) primary key, -- the md5 of each version of an image
image_width int,
image_length int
I want to then find either the largest (max) or smallest (min) version of an
image that falls within some range of sizes:
e.g.
select file_md5 from image_instance
where image_width =
(select min(image_width) from image_instance where md5 =
'546b94e94851a56ee721f3b755f58462')
and image_length =
(select min(image_length) from image_instance where md5 =
'546b94e94851a56ee721f3b755f58462')
and md5 = '546b94e94851a56ee721f3b755f58462'
and image_width between 0 and 160
and image_length between 0 and 160;
Now, having to do three selects on 'md5' to limit the search seems a little
unoptimal to me. Note that the test tables are small and I have no other
indexes apart from the 'primary key' constraint yet - this is not my primary
concern at this point, I would just like cleaner SQL.
All I want back is (for some definition) the 'file_md5' that best matches my
min/max criteria.
I have not - and will leave for now - the case where a cropped image results
in a scale change between width and length such that the min/max test
returns a different set of rows for each dimension. Argh.
And help given is greatly appreciated.
rgds,
--
Peter
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: help optimise this ?
@ 2002-11-21 14:51 Henshall, Stuart - Design & Print <SHenshall@westcountry-design-print.co.uk>
0 siblings, 0 replies; 4+ messages in thread
From: Henshall, Stuart - Design & Print @ 2002-11-21 14:51 UTC (permalink / raw)
To: Peter Galbavy <peter.galbavy@knowtion.net>; +Cc: pgsql-sql
Peter Galbavy wrote:
> I have a table of image 'instances' where the columns include:
>
> md5 char(32), -- the 'original' image md5 key
> file_md5 char(32) primary key, -- the md5 of each version of an
> image image_width int,
> image_length int
>
> I want to then find either the largest (max) or smallest (min)
> version of an image that falls within some range of sizes:
>
> e.g.
>
> select file_md5 from image_instance
> where image_width =
> (select min(image_width) from image_instance where md5 =
> '546b94e94851a56ee721f3b755f58462')
> and image_length =
> (select min(image_length) from image_instance where md5 =
> '546b94e94851a56ee721f3b755f58462')
> and md5 = '546b94e94851a56ee721f3b755f58462'
> and image_width between 0 and 160
> and image_length between 0 and 160;
>
> Now, having to do three selects on 'md5' to limit the search seems a
> little unoptimal to me. Note that the test tables are small and I
> have no other indexes apart from the 'primary key' constraint yet -
> this is not my primary concern at this point, I would just like
> cleaner SQL.
>
> All I want back is (for some definition) the 'file_md5' that best
> matches my min/max criteria.
>
> I have not - and will leave for now - the case where a cropped image
> results in a scale change between width and length such that the
> min/max test returns a different set of rows for each dimension. Argh.
>
> And help given is greatly appreciated.
>
> rgds,
> --
> Peter
>
If you are willing to use pgsqlism how about:
select file_md5 from image_instance WHERE
md5 = '546b94e94851a56ee721f3b755f58462' AND
image_width between 0 and 160 AND
image_length between 0 and 160 AND
ORDER BY image_width::int8*image_length::int8 LIMIT 1
This should get the smallest overall image size within your bounds.
It might be faster to do ORDER BY image_width,image_length LIMIT 1
but this wouldn't necessarily give the smallest if the aspect ratio changed
hth,
- Stuart
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: help optimise this ?
@ 2002-11-21 15:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Peter Galbavy <peter.galbavy@knowtion.net>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2002-11-21 15:46 UTC (permalink / raw)
To: Peter Galbavy <peter.galbavy@knowtion.net>; +Cc: pgsql-sql
"Peter Galbavy" <peter.galbavy@knowtion.net> writes:
> I want to then find either the largest (max) or smallest (min) version of an
> image that falls within some range of sizes:
Depends on how you want to define "largest" and "smallest", but if
"area" is a good enough definition, seems like this would work:
select file_md5 from image_instance
where md5 = '546b94e94851a56ee721f3b755f58462'
and image_width between 0 and 160
and image_length between 0 and 160
order by image_width * image_length
limit 1;
Probably an index on md5 would be sufficient to make this go fast ---
I assume you're not going to be storing a vast number of sizes of
the same image.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: help optimise this ?
@ 2002-11-21 15:49 Peter Galbavy <peter.galbavy@knowtion.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Peter Galbavy @ 2002-11-21 15:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Wow. Three people have replied with an effectively identical solution.
Why didn't I think of this ? Answers on a postcard to...
Thanks to all that have replied.
Peter
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Peter Galbavy" <peter.galbavy@knowtion.net>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, November 21, 2002 3:46 PM
Subject: Re: [SQL] help optimise this ?
> "Peter Galbavy" <peter.galbavy@knowtion.net> writes:
> > I want to then find either the largest (max) or smallest (min) version
of an
> > image that falls within some range of sizes:
>
> Depends on how you want to define "largest" and "smallest", but if
> "area" is a good enough definition, seems like this would work:
>
> select file_md5 from image_instance
> where md5 = '546b94e94851a56ee721f3b755f58462'
> and image_width between 0 and 160
> and image_length between 0 and 160
> order by image_width * image_length
> limit 1;
>
> Probably an index on md5 would be sufficient to make this go fast ---
> I assume you're not going to be storing a vast number of sizes of
> the same image.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-11-21 15:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-21 14:22 help optimise this ? Peter Galbavy <peter.galbavy@knowtion.net>
2002-11-21 15:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-21 15:49 ` Peter Galbavy <peter.galbavy@knowtion.net>
2002-11-21 14:51 Re: help optimise this ? Henshall, Stuart - Design & Print <SHenshall@westcountry-design-print.co.uk>
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