From: Shaun Thomas <sthomas@leapfrogonline.com>
To: pgsql-performance@postgresql.org
Subject: Re: any hope for my big query?
Date: Mon, 2 Oct 2006 10:32:35 -0500
Message-ID: <200610021032.36181.sthomas@leapfrogonline.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0609281505360.21293@GRD.cube42.tai.silentmedia.com>
References: <Pine.LNX.4.64.0609281505360.21293@GRD.cube42.tai.silentmedia.com>
On Thursday 28 September 2006 17:18, Ben wrote:
> explain select
> distinct public.album.id
> from
> public.album,public.albumjoin,public.track,umdb.node
> where
> node.dir=2811
> and albumjoin.album = public.album.id
> and public.albumjoin.track = public.track.id
> and levenshtein(substring(public.track.name for 75),
> substring(node.file for 75)) <= 10
> and public.album.id in
> (select album from albumjoin group by album having count(*) between
15 and 25)
> group by public.album.id
> having count(*) >= 5;
If I'm reading this right, you want all the albums with 15-25 entries in
album join having 5 or more tracks that are (soundex type) similar to
other nodes. Knowing that, you can also try something like this:
select a.album
from (select album,track from albumjoin group by album having count(1)
between 15 and 25) a
join public.track t on (a.track = t.id)
join umdb.node n on (levenshtein(substring(t.name for 75),
substring(n.file for 75)) < 9)
where n.dir = 2811
group by a.album
having count(1) > 4;
This removes two of your tables, since you were only interested in
albums with 15-25 albumjoins, and weren't actually using any album data
other than the ID, which albumjoin supplies. Your subselect is now an
integral part of the whole query, being treated like a temp table that
only supplies album IDs with 15-25 albumjoins. From there, add on the
track information, and use that to restrict the matching nodes. Your
explain should be better with the above.
Just remember with the levenshtein in there, you're forcing a sequence
scan on the node table. Depending on how big that table is, you may
not be able to truly optimize this.
--
Shaun Thomas
Database Administrator
Leapfrog Online
807 Greenwood Street
Evanston, IL 60201
Tel. 847-440-8253
Fax. 847-570-5750
www.leapfrogonline.com
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: sthomas@leapfrogonline.com
Subject: Re: any hope for my big query?
In-Reply-To: <200610021032.36181.sthomas@leapfrogonline.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