Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

Next:From: Tom LaneDate: 2002-10-23 14:31:18
Subject: Re: joining views
Previous:From: Ludwig LimDate: 2002-10-23 01:48:04
Subject: Re: Selective usage of index in planner/optimizer (Too conservative?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group