From: | "dex" <dex(at)bridge3(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Is there a performance between Inherits and Views? |
Date: | 2003-04-16 06:10:44 |
Message-ID: | AIEFJBJIIGJICIKAMAAIAECGCFAA.dex@bridge3.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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);
From | Date | Subject | |
---|---|---|---|
Next Message | Jonathan Moore | 2003-04-16 06:58:28 | dum query plan |
Previous Message | Tom Lane | 2003-04-15 23:37:06 | Re: Do Views offer any performance advantage? |