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=da6sgp3j+3HXJE2KUZ7TAs9kRQ4tLhNeu5+klFEA/k0iC8VY7sYwKYwW+nLA1ykkUiwBZAwS7oU8oBBNN5n+7t9CM2MDMA/TL/6MrWVgWXHH1Y1ES81VXjWLovNh64nNk+CEMWzA+ind8nfWimngZWJlLMNtX3pl8aJQuGXlxeg=
Message-ID: <b42b73150704261409j4472cc31xd069986f8a5f1269@mail.gmail.com>
Date: Thu, 26 Apr 2007 17:09:28 -0400
From: "Merlin Moncure" <mmoncure@gmail.com>
To: zardozrocks <zardozrocks@gmail.com>
Subject: Re: Simple query, 10 million records...MySQL ten times faster
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1177450006.547698.120920@u32g2000prd.googlegroups.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <1177450006.547698.120920@u32g2000prd.googlegroups.com>

On 24 Apr 2007 14:26:46 -0700, zardozrocks <zardozrocks@gmail.com> wrote:
> I have this table:
>
> CREATE TABLE test_zip_assoc (
>     id serial NOT NULL,
>     f_id integer DEFAULT 0 NOT NULL,
>     lat_radians numeric(6,5) DEFAULT 0.00000 NOT NULL,
>     long_radians numeric(6,5) DEFAULT 0.00000 NOT NULL
> );
> CREATE INDEX lat_radians ON test_zip_assoc USING btree (lat_radians);
> CREATE INDEX long_radians ON test_zip_assoc USING btree
> (long_radians);
>
>
>
> It's basically a table that associates some foreign_key (for an event,
> for instance) with a particular location using longitude and
> latitude.  I'm basically doing a simple proximity search.  I have
> populated the database with *10 million* records.  I then test
> performance by picking 50 zip codes at random and finding the records
> within 50 miles with a query like this:
>
> SELECT id
>         FROM test_zip_assoc
>         WHERE
>                 lat_radians > 0.69014816041
>                 AND lat_radians < 0.71538026567
>                 AND long_radians > -1.35446228028
>                 AND long_radians < -1.32923017502
>
>
> On my development server (dual proc/dual core Opteron 2.8 Ghz with 4GB
> ram) this query averages 1.5 seconds each time it runs after a brief
> warmup period.  In PostGreSQL it averages about 15 seconds.
>
> Both of those times are too slow.  I need the query to run in under a
> second with as many as a billion records.  I don't know if this is
> possible but I'm really hoping someone can help me restructure my
> indexes (multicolumn?, multiple indexes with a 'where' clause?) so
> that I can get this running as fast as possible.
>
> If I need to consider some non-database data structure in RAM I will
> do that too.  Any help or tips would be greatly appreciated.  I'm
> willing to go to greath lengths to test this if someone can make a
> good suggestion that sounds like it has a reasonable chance of
> improving the speed of this search.  There's an extensive thread on my
> efforts already here:

You can always go the earthdist route.  the index takes longer to
build (like 5x) longer than btree, but will optimize that exact
operation.

merlin