Search Postgresql Archives

Re: Lifting WHERE conditions out of inner select

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"John D. Burger" <john@xxxxxxxxx> writes:
> ... WHERE sense.synsetid
>      IN (SELECT synset2id FROM semlinkref
>           WHERE synset1id
>             IN (SELECT synsetid FROM sense
>                  WHERE wordid = (SELECT wordid FROM word WHERE  
> lemma='scramble'))
>           AND linkid=1
>           AND synset.pos='v')

> I realized that the last constraint, synset.pos='v', actually applies  
> to one of the tables in the main join, and could be lifted out of the  
> double IN clause.  Doing so sped the query up by a factor of 10,000.

> My question is, should the planner have figured this out, and we're  
> just losing out because we're stuck in 7.4?  Or is there some subtle  
> difference in semantics I'm missing?

As long as the condition involving the upper variable is STABLE
(including IMMUTABLE), I think your analysis is correct --- at least for
upper references that're within top-level WHERE clauses of a simple
subquery.  An example where it would not work is

	foo IN (SELECT COUNT(*) FROM sometable
		WHERE something-involving-upper-variable)

Here the upper condition doesn't simply filter out all the rows of the
subquery but actually changes the value of the (one) returned row.

The planner does not look for this type of situation though, and after
some study I think it'd be more trouble than it was worth.  It'd be
less than trivial to determine whether the upper references occurred
only in places where it was safe to pull them up, and the actual pulling
would take some code that doesn't exist now, too,

			regards, tom lane

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux