From: | Dean Rasheed <dean_rasheed(at)hotmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | RE: Performance problem (outer join + view + non-strict functions) |
Date: | 2007-11-18 22:13:58 |
Message-ID: | BAY113-W11A97133B50A9287B0B6CFF27D0@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ow Mun Heng | 2007-11-19 01:27:47 | Re: PostgreSQL vs MySQL, and FreeBSD |
Previous Message | gabor | 2007-11-18 19:26:12 | Re: autovacuum: recommended? |