X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 143158B9E8F for ; Thu, 3 Feb 2005 13:59:33 +0000 (GMT) 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 32903-04 for ; Thu, 3 Feb 2005 13:59:27 +0000 (GMT) Received: from mail.logi-track.com (www.logi-track.com [213.239.193.212]) by svr1.postgresql.org (Postfix) with ESMTP id 5F8838B9EE5 for ; Thu, 3 Feb 2005 13:59:29 +0000 (GMT) Received: from kingfisher.intern.logi-track.com (J2904.j.pppool.de [85.74.41.4]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.logi-track.com (Postfix) with ESMTP id DF5F2308DA; Thu, 3 Feb 2005 14:59:31 +0100 (CET) Received: from [127.0.0.1] (localhost [127.0.0.1]) by kingfisher.intern.logi-track.com (Postfix) with ESMTP id 3F9E0AB3FA; Thu, 3 Feb 2005 14:59:26 +0100 (CET) Message-ID: <42022E3D.80108@logi-track.com> Date: Thu, 03 Feb 2005 14:59:25 +0100 From: Markus Schaber User-Agent: Mozilla Thunderbird 1.0 (X11/20050116) X-Accept-Language: de-DE, de, en-us, en MIME-Version: 1.0 To: PostgreSQL Performance List , PostGIS Users Discussion Subject: Re: [postgis-users] Bad query optimizer misestimation because of References: <42010C24.90301@logi-track.com> In-Reply-To: <42010C24.90301@logi-track.com> X-Enigmail-Version: 0.90.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig19C3F8ACA0CD6AA10E7375B7" Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200502/66 X-Sequence-Number: 10395 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig19C3F8ACA0CD6AA10E7375B7 Content-Type: text/plain; charset=ISO-8859-15 Content-Transfer-Encoding: 8bit Hi, all, Markus Schaber schrieb: > As a small workaround, I could imagine to add a small additional column > in the table that contains the geometry's bbox, and which I use the && > operator against. This should avoid touching the TOAST for the skipped rows. For your personal amusement: I just noticed that, after adding the additional column containing the bbox and running VACUUM FULL, the table now has a size of 4 pages, and that's enough for the query optimizer to choose an index scan. At least that saves us from modifying and redeploying a bunch of applications to use the && bbox query:-) Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com --------------enig19C3F8ACA0CD6AA10E7375B7 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCAi49OVWsnapT9i0RAvpIAKDEGb4dF+YbWpC4Zr/P2JkfotCWIgCgjV1c F8uwjDcR5hnQ0yKt+Dfgch0= =WWJZ -----END PGP SIGNATURE----- --------------enig19C3F8ACA0CD6AA10E7375B7--