Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id C2DBE9FB691 for ; Mon, 12 Feb 2007 15:03:33 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 50851-04 for ; Mon, 12 Feb 2007 15:03:21 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id 2C7069FB6AE for ; Mon, 12 Feb 2007 15:03:21 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so1346203ugf for ; Mon, 12 Feb 2007 11:03:20 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=uEUrVMfEz8PtjsMNa/IRWDVLZurQVc4wNpjtBq7HfeklvE8wGePMFBxE0eG4rUIvuQsUvMezSaTbnpV6PIagX3XgbFLWvkNImAXnux3es4tcycUtlqUg4VcXIIttTN0ubY6lrq4sNOMYf2h+7YQ+UCWMl8mVFoNpBY2vs0kOAFk= Received: by 10.78.180.18 with SMTP id c18mr225294huf.1171306994104; Mon, 12 Feb 2007 11:03:14 -0800 (PST) Received: by 10.78.35.18 with HTTP; Mon, 12 Feb 2007 11:03:14 -0800 (PST) Message-ID: Date: Mon, 12 Feb 2007 14:03:14 -0500 From: "Merlin Moncure" To: "Mark Stosberg" Subject: Re: cube operations slower than geo_distance() on production server Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/115 X-Sequence-Number: 23169 On 2/12/07, Mark Stosberg wrote: > Merlin Moncure wrote: > > On 2/10/07, Mark Stosberg wrote: > >> > >> With the help of some of this list, I was able to successfully set up > >> and benchmark a cube-based replacement for geo_distance() calculations. > >> > >> On a development box, the cube-based variations benchmarked consistently > >> running in about 1/3 of the time of the gel_distance() equivalents. > >> > >> After setting up the same columns and indexes on a production > >> database, it's a different story. All the cube operations show > >> themselves to be about the same as, or noticeably slower than, the same > >> operations done with geo_distance(). > >> > >> I've stared at the EXPLAIN ANALYZE output as much I can to figure what's > >> gone. Could you help? > >> > >> Here's the plan on the production server, which seems too slow. Below > >> is the plan I get in > >> on the development server, which is much faster. > >> > >> I tried "set enable_nestloop = off", which did change the plan, but > >> the performance. > >> > >> The production DB has much more data in it, but I still expected > >> comparable results relative > >> to using geo_distance() calculations. > > > > any objection to posting the query (any maybe tables, keys, indexes, etc)? > > Here the basic query I'm using: > SELECT > -- 1609.344 is a constant for "meters per mile" > cube_distance( (SELECT earth_coords from zipcodes WHERE zipcode = > '90210') , earth_coords)/1609.344 > AS RADIUS > FROM pets > -- "shelters_active" is a view where "shelter_state = 'active'" > JOIN shelters_active as shelters USING (shelter_id) > -- The zipcode fields here are varchars > JOIN zipcodes ON ( > shelters.postal_code_for_joining = zipcodes.zipcode ) > -- search for just 'dogs' > WHERE species_id = 1 > AND pet_state='available' > AND earth_box( > (SELECT earth_coords from zipcodes WHERE zipcode = '90210') , > 10*1609.344 > ) @ earth_coords > ORDER BY RADIUS; your query looks a bit funky. here are the problems I see. * in your field list, you don't need to re-query the zipcode table. > cube_distance( (SELECT earth_coords from zipcodes WHERE zipcode = > '90210') , earth_coords)/1609.344 AS RADIUS becomes cube_distance(pets.earth_coords, earth_coords ) / 1609.344 AS RADIUS also, dont. re-refer to the zipcodes table in the join clause. you are already joining to it: > AND earth_box( > (SELECT earth_coords from zipcodes WHERE zipcode = '90210') , > 10*1609.344) @ earth_coords becomes AND earth_box(zipcodes.earth_coords, 10*1609.344) ) @ pets.earth_coords * also, does pet_state have any other states than 'available' and ' not available'? if not, you should be using a boolean. if so, you can consider a functional index to convert it to a booelan. * if you always look up pets by species, we can explore composite index columns on species, available (especially using the above functional suggestion), etc. composite > partial (imo) thats just to start. play with it and see what comes up. merlin