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 B226A9DCC6A for ; Tue, 6 Dec 2005 05:55:01 -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 77007-06 for ; Tue, 6 Dec 2005 05:55:03 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtpgw.computec.de (smtpgw.computec.de [212.123.108.8]) by postgresql.org (Postfix) with ESMTP id 3A9D29DCAEE for ; Tue, 6 Dec 2005 05:54:58 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtpgw.computec.de (Postfix) with ESMTP id 304A1559C4; Tue, 6 Dec 2005 10:55:00 +0100 (CET) Received: from smtpgw.computec.de ([127.0.0.1]) by localhost (localhost [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 29076-01-88; Tue, 6 Dec 2005 10:54:59 +0100 (CET) Received: from HERMES.computec.de (unknown [192.168.0.22]) by smtpgw.computec.de (Postfix) with ESMTP id 955FB55984; Tue, 6 Dec 2005 10:54:59 +0100 (CET) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Subject: Re: Can this query go faster??? Date: Tue, 6 Dec 2005 10:54:58 +0100 Message-ID: <28011CD60FB1724DBA4442E38277F6264A6E40@hermes.computec.de> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] Can this query go faster??? Thread-Index: AcX6SZF1s7NsNnVMQSKWUPaenoZiXwAAG5NQ From: "Markus Wollny" To: "Joost Kraaijeveld" , "Pgsql-Performance" X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at computec.de X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.319 required=5 tests=[AWL=-0.600, DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44] X-Spam-Score: 1.319 X-Spam-Level: * X-Archive-Number: 200512/153 X-Sequence-Number: 15974 Hi, > -----Urspr=FCngliche Nachricht----- > Von: pgsql-performance-owner@postgresql.org=20 > [mailto:pgsql-performance-owner@postgresql.org] Im Auftrag=20 > von Joost Kraaijeveld > Gesendet: Dienstag, 6. Dezember 2005 10:44 > An: Pgsql-Performance > Betreff: [PERFORM] Can this query go faster??? =20 > SELECT customers.objectid FROM prototype.customers,=20 > prototype.addresses WHERE customers.contactaddress =3D=20 > addresses.objectid ORDER BY zipCode asc, housenumber asc=20 > LIMIT 1 OFFSET 283745 >=20 > Explain: >=20 > Limit (cost=3D90956.71..90956.71 rows=3D1 width=3D55) > -> Sort (cost=3D90247.34..91169.63 rows=3D368915 width=3D55) > Sort Key: addresses.zipcode, addresses.housenumber > -> Hash Join (cost=3D14598.44..56135.75 rows=3D368915 = width=3D55) > Hash Cond: ("outer".contactaddress =3D "inner".objectid) > -> Seq Scan on customers (cost=3D0.00..31392.15 > rows=3D368915 width=3D80) > -> Hash (cost=3D13675.15..13675.15 rows=3D369315 = width=3D55) > -> Seq Scan on addresses (cost=3D0.00..13675.15 > rows=3D369315 width=3D55) >=20 > The customers table has an index on contactaddress and objectid. > The addresses table has an index on zipcode+housenumber and objectid. The planner chooses sequential scans on customers.contactaddress and = addresses.objectid instead of using the indices. In order to determine = whether this is a sane decision, you should run EXPLAIN ANALYZE on this = query, once with SET ENABLE_SEQSCAN =3D on; and once with SET = ENABLE_SEQSCAN =3D off;. If the query is significantly faster with = SEQSCAN off, then something is amiss - either you haven't run analyze = often enough so the stats are out of date or you have random_page_cost = set too high (look for the setting in postgresql.conf) - these two are = the "usual suspects". Kind regards Markus