X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 161399DCC6A
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Dec 2005 05:51:31 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 76279-02
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue,  6 Dec 2005 05:51:31 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2])
	by postgresql.org (Postfix) with ESMTP id 8CB139DCAEE
	for <pgsql-performance@postgresql.org>;
	Tue,  6 Dec 2005 05:51:27 -0400 (AST)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id BDF273093D; Tue,  6 Dec 2005 10:51:25 +0100 (MET)
From: Michael Riess <mlriess@gmx.de>
X-Newsgroups: pgsql.performance
Subject: Re: Can this query go faster???
Date: Tue, 06 Dec 2005 10:51:25 +0100
Organization: Hub.Org Networking Services
Lines: 29
Message-ID: <dn3muo$i9c$1@news.hub.org>
References: <1133862229.8837.61.camel@Panoramix>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@news.hub.org
User-Agent: Thunderbird 1.5 (Windows/20051025)
In-Reply-To: <1133862229.8837.61.camel@Panoramix>
To: pgsql-performance@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200512/151
X-Sequence-Number: 15972

> Hi,
> 
> Is it possible to get this query run faster than it does now, by adding
> indexes, changing the query?
> 
> SELECT customers.objectid FROM prototype.customers, prototype.addresses
> WHERE
> customers.contactaddress = addresses.objectid
> ORDER BY zipCode asc, housenumber asc
> LIMIT 1 OFFSET 283745
> 
> Explain:
> 
> Limit  (cost=90956.71..90956.71 rows=1 width=55)
>   ->  Sort  (cost=90247.34..91169.63 rows=368915 width=55)
>         Sort Key: addresses.zipcode, addresses.housenumber
>         ->  Hash Join  (cost=14598.44..56135.75 rows=368915 width=55)
>               Hash Cond: ("outer".contactaddress = "inner".objectid)
>               ->  Seq Scan on customers  (cost=0.00..31392.15
> rows=368915 width=80)
>               ->  Hash  (cost=13675.15..13675.15 rows=369315 width=55)
>                     ->  Seq Scan on addresses  (cost=0.00..13675.15
> rows=369315 width=55)
> 
> The customers table has an index on contactaddress and objectid.
> The addresses table has an index on zipcode+housenumber and objectid.

When the resulting relation contains all the info from both tables, 
indexes won't help, seq scan is inevitable.