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

Re: cube operations slower than geo_distance() on production server

From: "Merlin Moncure" <mmoncure(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Mark Stosberg" <mark(at)summersault(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: cube operations slower than geo_distance() on production server
Date: 2007-02-14 13:58:35
Message-ID: b42b73150702140558l3220d2e0v3e5e38d64b6e35c5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 2/14/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> There are two things wrong here: first, that the estimated row count is
> only 20% of actual; it should certainly not be that far off for such a
> simple condition.  I wonder if your vacuum/analyze procedures are
> actually working.  Second, you mentioned somewhere along the line that
> 'available' pets are about 10% of all the entries, which means that this
> indexscan is more than likely entirely counterproductive: it would be
> cheaper to ignore this index altogether.

I think switching the index on pet_state to a composite on (pet_state,
species_id) might help too.

or even better:

create function is_pet_available(text) returns bool as
$$
  select $1='available';
$$ language sql immutable;

create index pets_available_species_idx on
pets(is_pet_available(pet_state), species_id);

refactor your query something similar to:

SELECT * FROM
(
SELECT
 earth_coords(q.earth_coords, s.earth_coords)/1609.344 as radius
 FROM pets
 JOIN shelters_active as shelters USING (shelter_id)
 JOIN zipcodes s ON shelters.postal_code_for_joining = zipcodes.zipcode
 JOIN zipcodes q ON q.zipcode = '90210'
 WHERE
   is_pet_available(pet_state)
   AND species_id = 1
   AND earth_box(q.earth_coords, 10*1609.344) @ s.earth_coords
) p order by radius

merlin

In response to

Responses

pgsql-performance by date

Next:From: Luke LonerganDate: 2007-02-14 15:35:43
Subject: Re: Benchmarking PGSQL?
Previous:From: Shoaib MirDate: 2007-02-14 10:17:45
Subject: Re: Benchmarking PGSQL?

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