Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 4CA529FBFAB for ; Thu, 26 Apr 2007 20:14:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 45383-06 for ; Thu, 26 Apr 2007 20:14:31 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.225]) by postgresql.org (Postfix) with ESMTP id 6BC5D9FBFA7 for ; Thu, 26 Apr 2007 20:14:35 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id s1so244633nze for ; Thu, 26 Apr 2007 16:14:34 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=HMJHDVTHQZvuqHCjHk4vNRbx3Y7ZxbUxloA4v/ZHjls4S+yrVrJuWG4QHUci78k1FsaXKKTcF18s6rbi93Io1I+2l+1+3cJFU60px9sinj3al4TTpwtYOfq43VttTH0iKbBIf1IwCB3QfDqGYPZsG6F2RYY0ZfAaAYUab2Wa7d0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=Wmmwx/8jXUeEYIgsyJFaJqX/dezGchrSFeX6iiMUMzxWDe33m2OLInjTGNkt/PdJlaFuDl/uYiPDwdQMyZhaf7t34wCk3HcowGPY3C2DrEbWFta2j4pLfhs9WScd8onTayLQQJLAfwj4cjF/5vZM8BJ64JWWUDiSXR56KZeNK8s= Received: by 10.65.248.19 with SMTP id a19mr5099991qbs.1177629274269; Thu, 26 Apr 2007 16:14:34 -0700 (PDT) Received: by 10.65.177.7 with HTTP; Thu, 26 Apr 2007 16:14:34 -0700 (PDT) Message-ID: <88daf38c0704261614o5f367f2fyb7087d37f1eb64ce@mail.gmail.com> Date: Fri, 27 Apr 2007 01:14:34 +0200 From: "Alexander Staubo" To: zardozrocks Subject: Re: Simple query, 10 million records...MySQL ten times faster Cc: pgsql-performance@postgresql.org In-Reply-To: <88daf38c0704261612o741b67bvd380484cb38b814d@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <1177450006.547698.120920@u32g2000prd.googlegroups.com> <88daf38c0704261612o741b67bvd380484cb38b814d@mail.gmail.com> X-Google-Sender-Auth: 20fae11bbe5b1abe X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/448 X-Sequence-Number: 24325 On 4/27/07, Alexander Staubo wrote: [snip] > PostGIS implements the whole GIS stack, and it's so good at this that > it's practically the de facto tool among GIS analysts. Installing > PostGIS into a database is simple, and once you have done this, you > can augment your table with a geometry (*): > > alter table test_zip_assoc add column lonlat geometry; I forgot to include the crucial step, of course: create index test_zip_assoc_lonlat_index on test_zip_assoc using gist (lonlat gist_geometry_ops); analyze test_zip_assoc_lonlat; This creates a GiST index on the geometry and (significantly) updates the table statistics. Alexander.