X-Original-To: pgsql-hackers@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6CA14475E82; Mon, 5 May 2003 15:20:50 -0400 (EDT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id C50F1476397; Mon, 5 May 2003 15:20:39 -0400 (EDT) Received: from [66.219.92.2] (HELO temoku) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3009880; Mon, 05 May 2003 12:20:28 -0700 Content-Type: text/plain; charset="us-ascii" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: pgsql-performance , pgsql-hackers@postgresql.org Subject: Hypothetical suggestions for planner, indexing improvement Date: Mon, 5 May 2003 12:19:58 -0700 User-Agent: KMail/1.4.3 MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200305051219.58955.josh@agliodbs.com> X-Spam-Status: No, hits=-13.0 required=5.0 tests=BAYES_01,USER_AGENT_KMAIL 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/72 X-Sequence-Number: 38579 Folks, An area in which postgresql planner & indexing could be improved have occur= red=20 to me over the last week. I'd like to share this ideas with you in case = it=20 is worthy of the todo list. Please excuse me if this issue is already dealt with in CVS; I've been unab= le=20 to keep up completely on HACKERS lately. Please also excuse me if this=20 issue has been discussed and was tabled due to some theoretical limitation,= =20 such as x^n scaling problems THE IDEA: The planner should keep statistics on the correlation of foreign= =20 keys and apply them to the expected row counts for EXISTS clause limitation= s,=20 and possibly for other query types as well. To illustrate: Database "calendar" has two tables, events and event_days. Event_days has FK on column event_id to parent table Events. There is at lease one record in event_days for each record in events, and t= he=20 average parent-child relationship is 1 event -> 1.15 event_days records. This query: SELECT events.* FROM events WHERE EXISTS (SELECT event_id FROM event_days=20 WHERE event_day BETWEEN '2003-04-08' AND '2003-05-18'); Currently, (in 7.2.4 and 7.3.1) the planner makes the assumption that the= =20 above EXISTS restriction will only filter events by 50% and makes other joi= n=20 and execution plans accordingly. In fact, it filters events by 96% and th= e=20 ideal execution plan should be quite different. It would be really keen if planner statistics could be expanded to include= =20 correlation on foriegn keys in order to make more intelligent planner=20 decisions on the above type of query possible. Thanks for your attention! --=20 -Josh Berkus Aglio Database Solutions San Francisco