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 B95688B9B49 for ; Wed, 2 Feb 2005 17:44:44 +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 27400-05 for ; Wed, 2 Feb 2005 17:44:34 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 973F38B9B19 for ; Wed, 2 Feb 2005 17:44:34 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j12HiWFa000189; Wed, 2 Feb 2005 12:44:32 -0500 (EST) To: PostgreSQL Performance List Cc: PostGIS Users Discussion Subject: Re: Bad query optimizer misestimation because of TOAST tables In-reply-to: <42010C24.90301@logi-track.com> References: <42010C24.90301@logi-track.com> Comments: In-reply-to Markus Schaber message dated "Wed, 02 Feb 2005 18:21:40 +0100" Date: Wed, 02 Feb 2005 12:44:32 -0500 Message-ID: <188.1107366272@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/33 X-Sequence-Number: 10362 Markus Schaber writes: > IMHO, this tells the reason. The query planner has a table size of 3 > pages, which clearly is a case for a seqscan. But during the seqscan, > the database has to fetch an additional amount of 8225 toast pages and > 127 toast index pages, and rebuild the geometries contained therein. I don't buy this analysis at all. The toasted columns are not those in the index (because we don't support out-of-line-toasted index entries), so a WHERE clause that only touches indexed columns isn't going to need to fetch anything from the toast table. The only stuff it would fetch is in rows that passed the WHERE and need to be returned to the client --- and those costs are going to be the same either way. I'm not entirely sure where the time is going, but I do not think you have proven your theory about it. I'd suggest building the backend with -pg and getting some gprof evidence. regards, tom lane