Обсуждение: nested view with outer joins - best practices
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
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
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