Re: [PERFORM] *very* inefficient choice made by the planner (regarding

[SZUCS Gábor]

Dear Gurus,

- Original Message - 
From: Stephan Szabo [EMAIL PROTECTED]
Sent: Thursday, June 10, 2004 7:14 PM



 On Thu, 10 Jun 2004, Stephan Szabo wrote:

 
  On Thu, 10 Jun 2004, Jean-Luc Lachance wrote:
 
   I agree, but it should be a simple rewrite. No?
 
  It's NULLs inside the subselect that are the issue.
 
  select 1 in (select a from foo)
  select exists ( select 1 from foo where a=1)

Just a dumb try :)

  SELECT (exists(select 1 from foo where a isnull) AND NULL)
   OR exists(select 1 from foo where a=1)

AFAIK this returns
* NULL if (NULL in foo.a) and (1 not in foo.a)
* (1 in foo.a) otherwise.

The weakness is the doubled exists clause. I'm sure it makes most cases at
least doubtful...

G.
%--- cut here ---%
\end


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] *very* inefficient choice made by the planner (regarding

[Stephan Szabo]

On Fri, 18 Jun 2004, [iso-8859-1] SZUCS Gábor wrote:

 Dear Gurus,

 - Original Message -
 From: Stephan Szabo [EMAIL PROTECTED]
 Sent: Thursday, June 10, 2004 7:14 PM


 
  On Thu, 10 Jun 2004, Stephan Szabo wrote:
 
  
   On Thu, 10 Jun 2004, Jean-Luc Lachance wrote:
  
I agree, but it should be a simple rewrite. No?
  
   It's NULLs inside the subselect that are the issue.
  
   select 1 in (select a from foo)
   select exists ( select 1 from foo where a=1)

 Just a dumb try :)

   SELECT (exists(select 1 from foo where a isnull) AND NULL)
OR exists(select 1 from foo where a=1)

 AFAIK this returns
 * NULL if (NULL in foo.a) and (1 not in foo.a)
 * (1 in foo.a) otherwise.

 The weakness is the doubled exists clause. I'm sure it makes most cases at
 least doubtful...

Well, once you take into account the lhs being potentially null
 lhe in (select rhe from foo) is something like:

case when lhe is null then
 not exists(select 1 from foo limit 1) or null
else
 (exists(select 1 from foo where rhe is null) and null)
 or exists(select 1 from foo where rhe=lhe)
end

I think the real win occurs for where clause cases if it can pull up the
exists that references lhe so that it doesn't try to evaluate it on every
row and that's unlikely to occur in something like the above.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] *very* inefficient choice made by the planner (regarding

[Stephan Szabo]

On Thu, 10 Jun 2004, Frank van Vugt wrote:

 Could anybody explain why the planner is doing what it is doing?

 What could I do to make it easier to choose a better plan?

You might try raising sort_mem to see if it chooses a better plan.  I
think it may be guessing that the hash won't fit and falling back to the
plan you were getting.


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] *very* inefficient choice made by the planner (regarding IN(...))

[Tom Lane]

Frank van Vugt [EMAIL PROTECTED] writes:
 What could I do to make it easier to choose a better plan?

Increase sort_mem.  You want it to pick a hashed subplan, but
it's not doing so because 64000 rows won't fit in the default
sort_mem.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [PERFORM] *very* inefficient choice made by the planner (regarding IN(...))

[Frank van Vugt]

Wow,

The effectiveness of the pgsql mailinglists never ceases to amaze me.

Default sort mem it was, I guess I'd simply been to cautious with this 
per-client setting.




Stephan  Tom : thanks!




-- 
Best,




Frank.


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [PERFORM] *very* inefficient choice made by the planner (regarding

[Tom Lane]

Jean-Luc Lachance [EMAIL PROTECTED] writes:
 If the two statments are functionally equivalent, why can't PG rewrite 
 the NOT IN version into the more efficient NOT EXISTS?

They're not equivalent.  In particular, the behavior in the presence of
NULLs is quite different.

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] *very* inefficient choice made by the planner (regarding

[Stephan Szabo]

On Thu, 10 Jun 2004, Jean-Luc Lachance wrote:

 I agree, but it should be a simple rewrite. No?

It's NULLs inside the subselect that are the issue.

select 1 in (select a from foo)
select exists ( select 1 from foo where a=1)

If foo.a contains a row with NULL but no rows containing a 1, the above
give different results (unknown and exists) and IIRC, exists cannot
return unknown, so there's no simple rewrite of the subselect that gives
equivalent behavior.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster