Thread: joining views
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
Tomasz Myrta <jasiek@klaster.net> writes: > 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. I think this is the same issue that Stephan identified in his response to your other posting ("sub-select with aggregate"). When you write FROM x join y using (col) WHERE x.col = const the WHERE-restriction is only applied to x. I'm afraid you'll need to write FROM x join y using (col) WHERE x.col = const AND y.col = const Ideally you should be able to write just FROM x join y using (col) WHERE col = const but I think that will be taken the same as "x.col = const" :-( regards, tom lane
Użytkownik Tom Lane napisał: > I think this is the same issue that Stephan identified in his response > to your other posting ("sub-select with aggregate"). When you write > FROM x join y using (col) WHERE x.col = const > the WHERE-restriction is only applied to x. I'm afraid you'll need > to write > FROM x join y using (col) WHERE x.col = const AND y.col = const > Ideally you should be able to write just > FROM x join y using (col) WHERE col = const > but I think that will be taken the same as "x.col = const" :-( I am sad, but you are right. Using views this way will look strange: create view v3 as select v1.id as id1, v2.id as id2, ... from some_view v1, another_view v2; select * from v3 where id1=1234 and id2=1234; Is it possible to make it look better? And how to pass param=const to subquery ("sub-select with aggregate") if I want to create view with this query? Tomasz Myrta