Search Postgresql Archives

nested view with outer joins - best practices

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux