joining views
От | Tomasz Myrta |
---|---|
Тема | joining views |
Дата | |
Msg-id | 3DB65566.6060906@klaster.net обсуждение исходный текст |
Ответы |
Re: joining views
|
Список | pgsql-performance |
Hi I'd like to split queries into views, but I can't join them - planner search all of records instead of using index. It works very slow. Here is example: 1) create table1( id1 integer primary key, ...fields... ); table1 has thousands rows >40000. 2) create index ind_pkey on table1(id1); 3) create view some_view as select id1,...fields... from table1 join ...(10 joins); 4) create view another_view as select id1,...fields... from table1 join ... (5 joins) 4) Now here is the problem: explain select * from some_view where id1=1234; result: 100 explain select * from another_view where id1=1234; result: 80 explain select * from some_view v1, another_view v2 where v1.id1=1234 and v2.id1=1234 result: 210 Execution plan looks like planner finds 1 record from v1, so cost of searching v1 is about 100. After this planner finds 1 record from v2 (cost 80) and it's like I want to have. explain select * from some_view v1 join another_view v2 using(id1) where v1.id1=1234; result: 10000 (!) explain select * from some_view v1 join some_view v2 using(id1) where v1.id1=1234; result: 10000 (!) Even joining the same view doesn't work well. Execution plan looks like planner finds 1 record from v1, so cost of searching v1 is about 100. After this planner search all of records from v2 (40000 records, cost 9000) and then performs join with v1. I know that I can make only single view without joining views, but it makes me a big mess. Regards, Tomasz Myrta
В списке pgsql-performance по дате отправления: