
> > The reason that I mention EXISTS is because that's where the lack of 
> > cross-column corellation is most dramatic; the planner seems to estimate a 
> > flat 50% for EXISTS clauses regardless of the content.
> 
> No "seems to" about that one: see 
> src/backend/optimizer/path/clausesel.c
> 
> 	else if (is_subplan(clause))
> 	{
> 		/*
> 		 * Just for the moment! FIX ME! - vadim 02/04/98
> 		 */
> 		s1 = (Selectivity) 0.5;
> 	}

I think the main issue cannot be the correlation in this case.
In this FK PK case an events row exists for each row in the subselect, 
so the correlation must be above 1, how much above is irrelevant for the 
selectivity estimate (it is only relevant in the below 1 cases).
  
The selectivity in this case can be estimated by estimating the number of rows 
returned from the subselect where the (in the example missing) PK-FK join condition 
is removed (here: select distinct event_id from event_day where event_day BETWEEN 
'2003-04-08' AND '2003-05-18'). (selectivity = min (1, e. rows of subselect / e. rows of 
main select))

The "event_day BETWEEN '2003-04-08' AND '2003-05-18'" is what really reduces the
result set here, and that is not used.

Andreas

PS: in the example the subselect join clause event_id=events.event_id is missing


Tue, 6 May 2003 17:05:44 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
Re: Hypothetical suggestions for planner, indexing improvement 
Andreas,

> PS: in the example the subselect join clause event_id=events.event_id is
> missing

Sorry.  I was cutting-and-pasting to reduce the complexity of the query.   The 
full query is actually posted on -PERFORMANCE, as is a workaround to the 
"exists selectivity problem".

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Tue, 6 May 2003 08:17:51 -0700
Josh Berkus <josh@agliodbs.com>
Re: Hypothetical suggestions for planner, indexing improvement