Message-ID: <419759E1.8040500@egonet.pl>
Date: Sun, 14 Nov 2004 14:13:05 +0100
From: Jakub Kaniewski <jkan@egonet.pl>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; PL;
 rv:1.6) Gecko/20040113
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: View and subselect related questions
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 7bit

I have two data tables AUTHORS and BOOKS, and one indirection table 
AUTHOR_BOOKS which allow me to make n:m links. Now I want to create view 
that allow user to select all books of specyfic author - user should use 
query like this SELECT * FROM booksvw WHERE idauthor=xxx.

I know two queries that could solve my problem
1)SELECT * FROM books INNER JOIN authors_books ON 
book.id=authors_books.idbook AND authors_books.idauthor=:xxx
Alternative  I can use query
2)SELECT books.* FROM books WHERE id IN (SELECT authors_books.idbooks 
FROM authors_books WHERE authors_books.idauthor=:xxx)

I think that second query is faster in my case (most of books have only 
one author),

Logs :

1)
Merge Join  (cost=17.13..756.15 rows=5 width=116)
  Merge Cond: ("outer".id = "inner".idbook)
  ->  Index Scan using book_pkey on books  (cost=0.00..709.89 rows=11626 
width=116)
  ->  Sort  (cost=17.13..17.14 rows=5 width=4)
        Sort Key: autor_books.idbooks
        ->  Index Scan using autor_idx on autor_books  (cost=0.00..17.07 
rows=5 width=4)
              Index Cond: (idautor = 453)

2)Nested Loop  (cost=17.08..415.67 rows=1 width=116)
  Join Filter: ("inner".id = "outer".idbook)
  ->  HashAggregate  (cost=17.08..17.08 rows=1 width=4)
        ->  Index Scan using author_idx on autors_books  
(cost=0.00..17.07 rows=5 width=4)
              Index Cond: (idauthor = 453)
  ->  Seq Scan on books  (cost=0.00..253.26 rows=11626 width=116)

The first case I can easily transform to view. Second is harder. I don't 
know if there are a possibility to transport clauses from view WHERE 
part to sub selects. Is this possible?

James Kan