Re: [GENERAL] how to import "where exists（subquery）" EXISTS CONDITION performance?

[John R Pierce]

On 12/1/2015 11:51 PM, shili wrote:
I had saw this sentence: SQL statements that use the EXISTS condition 
in PostgreSQL are very inefficient since the sub-query is RE-RUN for 
EVERY row in the outer query's table. There are more efficient ways to 
write most queries, that do not use the EXISTS condition. So,I want to 
know how PostgreSQL to implement the EXISTS condition? Is that 
sentence true? and,if that is true,are there any methods to import the 
performance of the EXISTS condition? 


that would depend on the specific query with which you're having 
performance problems.


start with EXPLAIN ANALYZE SELECT restofyourquery;to see 
whats going on


--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] RE: [GENERAL] how to import "where exists（subquery）" EXISTS CONDITION performance?

[Albe Laurenz]

shili wrote:
> I had saw this sentence: SQL statements that use the EXISTS condition in 
> PostgreSQL are very inefficient
> since the sub-query is RE-RUN for EVERY row in the outer query's table. There 
> are more efficient ways
> to write most queries, that do not use the EXISTS condition.
> So,I want to know how PostgreSQL to implement the EXISTS condition? Is that 
> sentence true?
> and,if that is true,are there any methods to import the performance of the 
> EXISTS condition?

You mean "improve", not "import", right?

If you try it out you will find that PostgreSQL often executes an EXISTS clause 
as a join,
so no, SQL statements using EXISTS are not necessarily slow.

Still it is often a good idea to use an explicit join instead of EXISTS if 
possible.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Re: [GENERAL] how to import "where exists（subquery）" EXISTS CONDITION performance?

[David Rowley]

On 2 December 2015 at 20:51, shili  wrote:

> I had saw this sentence: SQL statements that use the EXISTS condition in
> PostgreSQL are very inefficient since the sub-query is RE-RUN for EVERY row
> in the outer query's table. There are more efficient ways to write most
> queries, that do not use the EXISTS condition. So,I want to know how
> PostgreSQL to implement the EXISTS condition? Is that sentence true? and,if
> that is true,are there any methods to import the performance of the EXISTS
> condition?


That sounds like it might be quite out-dated information. Semi joins have
been supported for around 7 years.

See
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e006a24ad152b3faec748afe8c1ff0829699b2e6

However, there are cases where this is still not possible, for example if
the subquery contains a volatile function, or perhaps a LIMIT or OFFSET.

You can view the output from the planner by prefixing your SELECT statement
with EXPLAIN: See
http://www.postgresql.org/docs/current/static/sql-explain.html
This will give you the information you need to see how the query planner
has decided on how your query will be executed.

--
 David Rowley   http://www.2ndQuadrant.com/

 PostgreSQL Development, 24x7 Support, Training & Services

[GENERAL] how to import "where exists（subquery）" EXISTS CONDITION performance?

[shili]

I had saw this sentence:   SQL statements that use the EXISTS condition in
PostgreSQL are very inefficient since the sub-query is RE-RUN for EVERY row
in the outer query's table. There are more efficient ways to write most
queries, that do not use the EXISTS condition.   So,I want to know how 
PostgreSQL to implement the  EXISTS condition? Is that sentence true? and,if
that is true,are  there any methods to import the performance of the EXISTS
condition?



--
View this message in context: 
http://postgresql.nabble.com/how-to-import-where-exists-subquery-EXISTS-CONDITION-performance-tp5875801.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.