[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    =?windows-1256?Q?RE:_[PERFORM]_Performance_problem_(outer_join_+_view_+_n?=
From:       Dean Rasheed <dean_rasheed () hotmail ! com>
Date:       2007-11-18 22:13:58
Message-ID: BAY113-W11A97133B50A9287B0B6CFF27D0 () phx ! gbl
[Download RAW message or body]


Ah yes, I see the problem. I see that it is also going to be a problem where I have \
used CASE..WHEN in the select list of views :-(

Naively, couldn't the subquery be pulled up if any non-nullable columns from the \
right table t2 were automatically wrapped in a simple function which returned NULL \
when the table row isn't matched (eg. when t2.ctid is NULL)? I'm a complete newbie to \
Postgres, so I have no idea if this is really possible or how hard it would be to \
implement in practice.

Dean.


> > I am having performance problems running a number of queries
> > involving views based on non-strict functions. I have reproduced the
> > problem with the simple test-case below which shows how the query plan
> > is different depending on whether the view uses strict or non-strict
> > functions (even though those columns do not appear in the WHERE
> > clause).
> 
> Subqueries that produce non-nullable output columns can't be pulled up
> underneath the nullable side of an outer join, because their output
> values wouldn't go to NULL properly when expanding an unmatched row
> from the other side of the join (see has_nullable_targetlist in
> prepjointree.c). In this context that means that we can't recognize
> the option of using a inner indexscan for the table within the subquery.
> 
> I have some vague ideas about how to eliminate that restriction,
> but don't hold your breath. At the earliest it might happen in 8.4.
> 
> regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/docs/faq

_________________________________________________________________
Feel like a local wherever you go.
http://www.backofmyhand.com

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate


[prev in list] [next in list] [prev in thread] [next in thread]