From: | Tomasz Myrta <jasiek(at)klaster(dot)net> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | joining views |
Date: | 2002-10-23 07:53:10 |
Message-ID: | 3DB65566.6060906@klaster.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-10-23 14:31:18 | Re: joining views |
Previous Message | Ludwig Lim | 2002-10-23 01:48:04 | Re: Selective usage of index in planner/optimizer (Too conservative?) |