views vs. equijoins re: speed

xzilla

(useing postgres) Im curious if there would be any performance gain by createing a view for a perticular query (then doing a select *) vs. useing a query that has about 12 equijoins in the where clause. My initial thought is no, so the db is still doing the work to process the view, but maybe I am overlooking something? TIA

Anon

You bring the data retrieval problem to a high level abstraction, if nothing else. Views are, pragmatically, named queries. It makes sense to do them, even though there may be no inherent performance advantage. I'm uncertain as to whether the database stores a parsed copy of the SQL or the plain SQL string. If it does, there is a small, but insignificant, speedup.

smarlowe

Views are generally no faster than a large equi-join, they simply make the join easier to access from program space since all you need is "select * from view" in you program code, and if you decide that you need to add a table to your view, all the programs that use that view will see the extra columns added with no code changes needed.

Performance enhancement comes from judicious use of indexes. Have you used the "explain" command in postgres? It's a good way to see if your indexing / sql code is running efficiently or not.

For instance on two tables, t1 and t2, where t2 is a joined table to t1, indexing t2's join column (the one with the id for rows from t1) the explain command goes from this:

explain select * from t1, t2 where t1.id=t2.t1_id;
NOTICE: QUERY PLAN:

Merge Join (cost=69.83..153.83 rows=10000 width=32)
-> Index Scan using t1_id_key on t1 (cost=0.00..59.00 rows=1000 width=16)
-> Sort (cost=69.83..69.83 rows=1000 width=16)
-> Seq Scan on t2 (cost=0.00..20.00 rows=1000 width=16)

to this:

explain select * from t1, t2 where t1.id=t2.t1_id;
NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..9.27 rows=10 width=32)
-> Seq Scan on t2 (cost=0.00..1.01 rows=1 width=16)
-> Index Scan using t1_id_key on t1 (cost=0.00..8.14 rows=10 width=16)

Notice the drop in cost from a range of 69 to 152 for the unindexed join to 0 to 9 for a properly indexed join.

Note that indexes can slow down inserts, but in postgresql, if you are doing a lot of inserts and put them in a transaction they'll fly (i.e. one insert or a hundred are about the same inside a transaction.)