joining views

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

Responses

Browse pgsql-performance by date

  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?)