X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 3FCA7D1B1B2 for ; Thu, 13 May 2004 12:16:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 30046-06 for ; Thu, 13 May 2004 12:16:35 -0300 (ADT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 2C2F6D1B1B3 for ; Thu, 13 May 2004 12:16:35 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 7F0F03576D; Thu, 13 May 2004 08:16:34 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 7D8DB3576C; Thu, 13 May 2004 08:16:34 -0700 (PDT) Date: Thu, 13 May 2004 08:16:34 -0700 (PDT) From: Stephan Szabo To: Fabio Panizzutti Cc: 'Shridhar Daithankar' , pgsql-performance@postgresql.org Subject: Re: R: Query plan on identical tables differs . Why ? In-Reply-To: <006301c438f3$6ca33460$3c02020a@ufficio> Message-ID: <20040513080130.E3465@megazone.bigpanda.com> References: <006301c438f3$6ca33460$3c02020a@ufficio> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/125 X-Sequence-Number: 6956 On Thu, 13 May 2004, Fabio Panizzutti wrote: > I don't understand why the planner chose a different query plan on > identical tables with same indexes . Because it's more than table structure that affects the choice made by the planner. In addition the statistics about the values that are there as well as the estimated size of the table have effects. One way to see is to see what it thinks is best is to remove the indexes it is using and see what plan it gives then, how long it takes and the estimated costs for those plans. In other suggestions, I think having a (tag_id, data_tag) index rather than (data_tag, tag_id) may be a win for queries like this. Also, unless you're doing many select queries by only the first field of the composite index and you're not doing very many insert/update/deletes, you may want to drop the other index on just that field.