View and subselect related questions
От | Jakub Kaniewski |
---|---|
Тема | View and subselect related questions |
Дата | |
Msg-id | 419759E1.8040500@egonet.pl обсуждение исходный текст |
Список | pgsql-sql |
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_pkeyon 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_idxon 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.08rows=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=11626width=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
В списке pgsql-sql по дате отправления: