Обсуждение: 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