X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D7E07475CA9 for ; Tue, 6 May 2003 07:49:18 -0400 (EDT) Received: from lakemtao01.cox.net (lakemtao01.cox.net [68.1.17.244]) by postgresql.org (Postfix) with ESMTP id 881A5474E42 for ; Tue, 6 May 2003 07:49:17 -0400 (EDT) Received: from [192.168.1.1] ([68.11.66.83]) by lakemtao01.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030506114920.LYFI8337.lakemtao01.cox.net@[192.168.1.1]> for ; Tue, 6 May 2003 07:49:20 -0400 Subject: Re: [HACKERS] Hypothetical suggestions for planner, From: Ron Johnson To: PgSQL Performance ML In-Reply-To: <22292.1052195133@sss.pgh.pa.us> References: <200305051219.58955.josh@agliodbs.com> <22292.1052195133@sss.pgh.pa.us> Content-Type: text/plain Organization: Message-Id: <1052221755.3368.286.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.4 Date: 06 May 2003 06:49:15 -0500 Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-44.3 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,SIGNATURE_LONG_DENSE, USER_AGENT_XIMIAN autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/59 X-Sequence-Number: 1838 On Mon, 2003-05-05 at 23:25, Tom Lane wrote: > Josh Berkus writes: > > THE IDEA: The planner should keep statistics on the correlation of foreign > > keys and apply them to the expected row counts for EXISTS clause limitations, > > and possibly for other query types as well. > > It's a thought. Keeping complete cross-column correlation stats (for > every combination of columns in the DB) is obviously out of the > question. If you're gonna do it you need a heuristic to tell you which > combinations of columns are worth keeping track of --- and foreign-key > relationships seem like a reasonable guide to the interesting > combinations. How about generalizing this into something useful for all queries? And to make this problem not just guess work on the part of the optimizer, how about having a separate "backslash command" so that the DBA can add specific/important/crucial predicates that he needs optimized. Thus, if there's a query with a large WHERE clause that has an optimized predicate inside it, the statistics would be used. > WHERE event_day BETWEEN '2003-04-08' AND '2003-05-18') What this sounds to me like, though, is that in order for it to work quickly, the postmaster would have to keep track in system tables each value of event_day and it's COUNT(*), thus more I/O overhead. -- +---------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | The purpose of the military isn't to pay your college tuition | | or give you a little extra income; it's to "kill people and | | break things". Surprisingly, not everyone understands that. | +---------------------------------------------------------------+