X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 865F5475F1B for ; Sun, 4 May 2003 14:00:33 -0400 (EDT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 85DC7475F00 for ; Sun, 4 May 2003 14:00:29 -0400 (EDT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3007280; Sun, 04 May 2003 11:00:28 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Tom Lane Subject: Re: Suggestions wanted for 7.2.4 query Date: Sun, 4 May 2003 10:59:41 -0700 User-Agent: KMail/1.4.3 Cc: PgSQL Performance ML References: <200305031928.52906.josh@agliodbs.com> <200305040907.03306.josh@agliodbs.com> <4612.1052068989@sss.pgh.pa.us> In-Reply-To: <4612.1052068989@sss.pgh.pa.us> MIME-Version: 1.0 Message-Id: <200305041059.41468.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-32.4 required=5.0 tests=BAYES_01,IN_REP_TO,QUOTED_EMAIL_TEXT,REFERENCES, REPLY_WITH_QUOTES,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/42 X-Sequence-Number: 1821 Tom, > 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". That may be solvable without forcing a seq scan on "events", simply by=20 overdetermining the criteria on date. That is, I can't apply the date=20 criteria to "events" because that would require running date calucations on= =20 each row forcing a seq scan ( i.e. (event_date + duration) between date_one= =20 and date_two would require a seq scan), but I can apply a broadend version = of=20 the criteria to "events" ( i.e. event_date between (date_one - 1 month) and= =20 (date_two + 1 day)) which would give the planner the idea that it is=20 returning a minority of rows from "events". Someday, we have to come up with a way of indexing simple multi-column=20 calculations. Unless someone did that in current source while I was behin= d=20 on -hackers? > 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. Hmm. Any hope of improving this in the future? Like the IN() functionali= ty=20 improvements in 7.4? > 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. No, we're probably cached ... the machine has 1gb of RAM. Also it has a= =20 really fast RAID array, at least for block disk reads, although random seek= =20 times suck. I can tweak a little. The problem is that it's a production= =20 machine in use 70 hours a week, so there isn't a lot of time we can test=20 performance settings that might cause problems. Thanks for the advice! --=20 Josh Berkus Aglio Database Solutions San Francisco