X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E0A57D7045 for ; Fri, 2 Sep 2005 01:24:38 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 91524-06 for ; Fri, 2 Sep 2005 04:24:36 +0000 (GMT) Received: from linda-2.paradise.net.nz (bm-2a.paradise.net.nz [202.0.58.21]) by svr1.postgresql.org (Postfix) with ESMTP id 97F7AD7039 for ; Fri, 2 Sep 2005 01:24:35 -0300 (ADT) Received: from smtp-2.paradise.net.nz (smtp-2a.paradise.net.nz [202.0.32.195]) by linda-2.paradise.net.nz (Paradise.net.nz) with ESMTP id <0IM6009GJ9KY4Z@linda-2.paradise.net.nz> for pgsql-performance@postgresql.org; Fri, 02 Sep 2005 16:24:34 +1200 (NZST) Received: from [192.168.1.11] (218-101-45-29.paradise.net.nz [218.101.45.29]) by smtp-2.paradise.net.nz (Postfix) with ESMTP id 883C39E21D; Fri, 02 Sep 2005 16:24:34 +1200 (NZST) Date: Fri, 02 Sep 2005 16:24:32 +1200 From: Mark Kirkwood Subject: Re: Massive performance issues In-reply-to: <20050901174231.GE23970@pongo.lshift.net> To: Matthew Sackman Cc: pgsql-performance@postgresql.org Message-id: <4317D400.2080809@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050726) References: <20050901174231.GE23970@pongo.lshift.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.137 required=5 tests=[AWL=0.137] X-Spam-Level: X-Archive-Number: 200509/42 X-Sequence-Number: 14350 Matthew Sackman wrote: > I need to get to the stage where I can run queries such as: > > select street, locality_1, locality_2, city from address > where (city = 'Nottingham' or locality_2 = 'Nottingham' > or locality_1 = 'Nottingham') > and upper(substring(street from 1 for 1)) = 'A' > group by street, locality_1, locality_2, city > order by street > limit 20 offset 0 > > and have the results very quickly. > This sort of query will be handled nicely in 8.1 - it has bitmap and/or processing to make use of multiple indexes. Note that 8.1 is in beta now. Cheers Mark