Views, indices and pg_dump

From: "Zak McGregor" <zak@mighty.co.za>

Hi all

Thanks to everyone for this great forum, and for Postgres!

I have a couple of questions:

I have a smallish table which I join on another (smaller) table. I have
created a view for this purpose, thinking it would save on overhead.
The view also incorporates some calculated fields. However, when I do an
EXPLAIN SELECT * FROM my_view it shows sequential scans on both tables and
no use of any indices (I've created them on the view and the two other
tables). It seems the view does the join on the fly. If so, will using a
view speed up queries? Also, how do I make it use the indices for queries?

OK - a view is nothing more than a pre-built query. It is useful for
ease-of-use and controlling which users can see which parts of your
database. It isn't going to gain you anything in performance.

If the EXPLAIN shows sequential scans, you'll need to look into the costs
listed for each stage of the query and how many rows PG thinks will be
returned. There are some items on explain in the performance-tips section of
the user manual, Bruce's book (link on www.postgresql.org) and in my
PostgreSQL notes (techdocs.postgresql.org)

If you want assistance with the explain please post the view definition, its
table definitions along with the explain.

Another, slightly unrelated question: Why does pg_dump dump the view as a
normal table?

Mine dumps as a CREATE VIEW statement - can you provide an example (with
which version of PG you are using).

HTH

- Richard Huxton

"Richard Huxton" <dev@archonet.com> writes:

Another, slightly unrelated question: Why does pg_dump dump the view as a
normal table?

Mine dumps as a CREATE VIEW statement - can you provide an example (with
which version of PG you are using).

IIRC, all pre-7.1 releases will dump views as CREATE TABLE + CREATE
RULE. Which works, but exposes the underlying implementation more than
it should.

regards, tom lane