X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 62759476331 for ; Sun, 4 May 2003 13:23:12 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 6C655475F00 for ; Sun, 4 May 2003 13:23:11 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h44HN9U6004613; Sun, 4 May 2003 13:23:10 -0400 (EDT) To: Josh Berkus Cc: PgSQL Performance ML Subject: Re: Suggestions wanted for 7.2.4 query In-reply-to: <200305040907.03306.josh@agliodbs.com> References: <200305031928.52906.josh@agliodbs.com> <25878.1052023320@sss.pgh.pa.us> <200305040907.03306.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Sun, 04 May 2003 09:07:03 -0700" Date: Sun, 04 May 2003 13:23:09 -0400 Message-ID: <4612.1052068989@sss.pgh.pa.us> From: Tom Lane X-Spam-Status: No, hits=-32.3 required=5.0 tests=BAYES_00,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES 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/41 X-Sequence-Number: 1820 Josh Berkus writes: >> This at least gives you some glimmer of a chance that the restriction on >> event_day can be used to avoid computing the entire join represented by >> sv_events. With the exists() form, there's no chance... > Hmmm. I have to take that back (must have been out too late last night ;-)). The EXISTS subquery *is* getting pushed down to become a restriction on events alone; that's what the "SubPlan" is. However, it'd still be worth looking for another way to express it, because the planner is pretty clueless about the selectivity of EXISTS restrictions. That's what's causing it to drastically overestimate the number of rows taken from "events" (14812 vs 1919), which in turn drives it away from using the nestloop-with-inner-indexscan join style for joining to "cases". > Are you saying that the planner being vague about what will be > returned from the EXISTS clause is what's triggering the seq scan on > "cases"? Right. The nestloop/indexscan style only wins if there are not too many outer rows. If the EXISTS constraint actually did succeed for 14812 "events" rows, the planner would probably be making the right choice to use a hash join. BTW, have you tried lowering the value of "random_page_cost"? Looking at the relative costs in these examples makes me think most of your tables are cached in memory. Of course, if that's not true during day-to-day production then you need to be wary about reducing the setting. regards, tom lane