Thread: how to import "where exists(subquery)" EXISTS CONDITION performance?
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: how to import "where exists(subquery)" EXISTS CONDITION performance?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
View this message in context: how to import "where exists(subquery)" EXISTS CONDITION performance?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] how to import "where exists(subquery)" EXISTS CONDITION performance?
From
David Rowley
Date:
On 2 December 2015 at 20:51, shili <shi_li_1992@163.com> 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.
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/
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
RE: [GENERAL] how to import "where exists(subquery)" EXISTS CONDITION performance?
From
Albe Laurenz
Date:
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
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