From: Jakub Kaniewski <jkan@egonet.pl>
To: pgsql-sql@postgresql.org
Subject: View and subselect related questions
Date: Sun, 14 Nov 2004 14:13:05 +0100
Message-ID: <419759E1.8040500@egonet.pl> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jkan@egonet.pl
Subject: Re: View and subselect related questions
In-Reply-To: <419759E1.8040500@egonet.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox