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
