nested view with outer joins - best practices

Started by Bohdan Lindaalmost 18 years ago3 messagesgeneral
Jump to latest
#1Bohdan Linda
bohdan.linda@seznam.cz

Hello,

I have noted one very strange thing which I would like to discuss with
you. I have noted that outer joins on nested views takes heavily longer
than the inner ones. Example:

REATE VIEW ports_view AS
SELECT
ports.pid,
nodes.nname
FROM
ports JOIN nodes ON nodes.nid = ports.pnode;

EXPLAIN ANALYZE
SELECT
*
FROM
services_subints LEFT JOIN ports_view as prts ON services_subints.port = prts.pid

http://explain-analyze.info/query_plans/2078-query-plan-811

but if I rewrote the view as:

SELECT
*
FROM
(services_subints LEFT JOIN ports as prts ON services_subints.port = prts.pid)
INNER JOIN nodes AS prn ON prts.pnode = prn.nid

http://explain-analyze.info/query_plans/2079-query-plan-812

if I revert to original nested view and use inner join I get similar plan as above.

Here is my question:

1) What are the best practices, if I want to use nested views?
2) Will my plan get better with new version of pgsql ( currently its 8.0.x )

Thank you,
Bohdan

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Bohdan Linda (#1)
Re: nested view with outer joins - best practices

Bohdan Linda <bohdan.linda@seznam.cz> writes:

2) Will my plan get better with new version of pgsql ( currently its 8.0.x )

8.0 is incapable of reordering outer joins, which is likely the cause of
your problem.

regards, tom lane

#3Bohdan Linda
bohdan.linda@seznam.cz
In reply to: Tom Lane (#2)
Re: nested view with outer joins - best practices

On Mon, Jun 09, 2008 at 04:41:16PM +0200, Tom Lane wrote:

8.0 is incapable of reordering outer joins, which is likely the cause of
your problem.

Thank you, will try.

Bohdan