Skip site navigation (1) Skip section navigation (2)

Re: help optimise this ?

From: "Henshall, Stuart - Design & Print" <SHenshall(at)westcountry-design-print(dot)co(dot)uk>
To: 'Peter Galbavy' <peter(dot)galbavy(at)knowtion(dot)net>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: help optimise this ?
Date: 2002-11-21 14:51:29
Message-ID: E382B5D8EDE1D6118DBE0008C759BCD6116ACC@WCPEXCHANGE (view raw, whole thread or download thread mbox)
Lists: 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
- Stuart

pgsql-sql by date

Next:From: Achilleus MantziosDate: 2002-11-21 14:59:10
Subject: Re: [SQL] psql on FreeBSD 4.7-RELEASE-p2 and greek (iso8859-7) chars
Previous:From: Tom LaneDate: 2002-11-21 14:47:20
Subject: Re: why the difference?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group