nested view with outer joins - best practices
| От | Bohdan Linda |
|---|---|
| Тема | nested view with outer joins - best practices |
| Дата | |
| Msg-id | 20080609132825.GA19970@bafster.baflabs.org обсуждение исходный текст |
| Ответы |
Re: nested view with outer joins - best practices
|
| Список | pgsql-general |
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
В списке pgsql-general по дате отправления: