Is there a performance between Inherits and Views?

Поиск
Список
Период
Сортировка
От dex
Тема Is there a performance between Inherits and Views?
Дата
Msg-id AIEFJBJIIGJICIKAMAAIAECGCFAA.dex@bridge3.com
обсуждение исходный текст
Список pgsql-performance
Hello,

In building a schema, I'd like to know if it makes sense
from a performance standpoint to use views instead of
an object oriented structure (i.e. inherits).

I would guess that the overhead of the queries against
inherited tables is higher than queries against views,
but I don't know.

In the cities / capitals example below, I could make
queries such as:

SELECT name FROM capitals;

or

SELECT name FROM capital_cities;

But which one would be faster?  In my real world example,
I will have one small base object table (i.e. cities in
the example) and many direct descendents of that base
table (e.g. capitals, beaches, national parks, suburbs
in the example).  This could be implemented as one
small base table and with many tables inheriting from
the base.  Or, it could be implemented as one larger
(but not huge) lookup table with many views.

What's the better choice from a performance standpoint?

Thanks!

--dex


--
--  Schema with Inherits
--
CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- (in ft)
  );

  CREATE TABLE capitals (
    state           char(2)
  ) INHERITS (cities);


--
--  Schema with View
--
CREATE TABLE all_cities (
    name            text,
    population      float,
    altitude        int,
    state           char(2)
);

CREATE VIEW just_cities AS SELECT
    all_cities.name,
    all_cities.population,
    all_cities.altitude
FROM all_cities;

-- or perhaps with a where clause, as in
CREATE VIEW capital_cities AS SELECT
    all_cities.name,
    all_cities.population,
    all_cities.altitude
FROM all_cities WHERE (all_cities.state IS NOT NULL);


В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Do Views offer any performance advantage?
Следующее
От: Andreas Pflug
Дата:
Сообщение: Re: Do Views offer any performance advantage?