From: Mark Stosberg <mark@summersault.com>
To: pgsql-performance@postgresql.org
Subject: Re: cube operations slower than geo_distance() on production server
Date: Mon, 12 Feb 2007 14:48:58 -0500
Message-ID: <eqqgc5$kle$1@news.hub.org> (raw)
In-Reply-To: <b42b73150702121103l297b690w84dde8ef9ef8d2f1@mail.gmail.com>
References: <eqiht3$2fvu$1@news.hub.org>
<b42b73150702091731x57996613hb50af0296007436c@mail.gmail.com>
<eqq3k3$1mpu$1@news.hub.org>
<b42b73150702121103l297b690w84dde8ef9ef8d2f1@mail.gmail.com>
Merlin--
Thanks so much for your help. Some follow-ups are below.
Merlin Moncure wrote:
>
>> 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
It may not have been clear from the query, but only the 'zipcodes' table
has an 'earth_coords' column. Also, I think your refactoring means
something different. My query expresses "number of miles this pet is
from 90210", while I think the refactor expresses a distance between a
pet and another calculated value.
> 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
I have the same question here as above-- I don't see how the new syntax
includes the logic of "distance from the 90210 zipcode".
> * 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.
Yes, it has three states.
> * 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)
We nearly always search by species. Right now it's mostly dogs and some
cats. I searched for references to composite index columns, and didn't
find much. Could you provide a direct reference to what you have in
mind?
Any other ideas appreciated!
Mark
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: mark@summersault.com
Subject: Re: cube operations slower than geo_distance() on production server
In-Reply-To: <eqqgc5$kle$1@news.hub.org>
* 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