2

Is there a (big) performance difference between running a query over a view or over the associated view-query with a where clause?

E.g. 

CREATE VIEW v_comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';

Is there a performance difference between these queries? 

1: SELECT * FROM v_comedies WHERE filmName LIKE 'Bat%'
2: SELECT * FROM films WHERE kind = 'Comedy' AND filmName LIKE 'Bat%'
Jan Doggen
  • 8,799
  • 13
  • 70
  • 144
stUrb
  • 6,612
  • 8
  • 43
  • 71
  • 1
    you may find your answer here: http://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query – Parvez Sep 13 '13 at 20:49
  • 4
    @Parvez: I don't believe your comment is helpful. The answer that you refer to describes behavior of Microsoft SQL Server which is different from the PostgreSQL behavior. – Olaf Sep 13 '13 at 20:52

1 Answers1

3

In general, views in PostgreSQL are stored as SELECT queries, so the performance should be identical between the two queries in your question.

If the films table is updated infrequently, say only once a day, but the queries are executed frequently, you can use a materialized view. It would behave as a table created with CTAS (CREATE TABLE ... AS ...), but would also store the SQL query that was used to create it, so you could refresh the materialized view on demand.

In general, views are used for joins, not for selections.

Olaf
  • 6,249
  • 1
  • 19
  • 37
  • 3
    Note that the materialized view option is only available starting with version 9.3. Prior to that you can fake it by starting with a "create table as ..." then periodically "truncate table ... ; insert into ... select ..." – gsiems Sep 13 '13 at 20:55
  • Thanks, @gsiems! I meant to say that. – Olaf Sep 13 '13 at 20:59
  • 1
    The films table is just an example i got from the postgresql database. My table would be updated frequently so the materialized view is not a good option. Your last sentence does the trick. The views I want to create are just sub-selections from the big table. So I just have to write bigger where clausules. Views would be very handy to make the sql code cleare to understand. – stUrb Sep 13 '13 at 21:16