pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: PgSQL Performance ML <pgsql-performance@postgresql.org>
Subject: Re: Suggestions wanted for 7.2.4 query
Date: Sun, 04 May 2003 13:23:09 -0400
Message-ID: <4612.1052068989@sss.pgh.pa.us> (raw)
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>

Josh Berkus <josh@agliodbs.com> 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




view thread (7+ messages)  latest in thread

Message-ID: <4612.1052068989@sss.pgh.pa.us>
Permalink:  ../4612.1052068989@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/4612.1052068989@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, josh@agliodbs.com
  Subject: Re: Suggestions wanted for 7.2.4 query
  In-Reply-To: <4612.1052068989@sss.pgh.pa.us>
  References: <200305031928.52906.josh@agliodbs.com> <25878.1052023320@sss.pgh.pa.us> <200305040907.03306.josh@agliodbs.com> <4612.1052068989@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox