How to join materalized view to child tables

From: "Postgres User" <postgres(dot)developer(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How to join materalized view to child tables
Date: 2007-07-10 06:03:09
Message-ID: b88c3460707092303w59cbaaah3c587cca8afc5ab6@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I have a quasi materialized view that's maintained by INS, UPD, and
DEL triggers on several child tables.

The tables involved have different structures, but I needed a single
view for selecting records based on a few common fields. This
approach is much faster than querying the separate tables and trying
to correlate and sort the results.

materialized view - view_a
child tables - table_a, table_b, table_c

Here's my question- what's the fastest what to retrieve rows from each
of the child tables after I get results from view_a ?
I don't like using temp tables in Postgres (too much pain in the
past), so first selecting into a temp table which could subsequently
be joined against the child tables isn't appealing to me.

The result set from materialized view_a will never exceed 60 rows, so
I'm thinking about this:
a) LOOP on a SELECT FROM view_a
b) for each record, add the row id to one of 3 comma delimited strings
(one per child table)
c) perform a SELECT WHERE IN (delimited_string) from each child table

Any comments? I guess I'm mainly concerned about the speed of the FOR
SELECT LOOP...

Browse pgsql-general by date

  From Date Subject
Next Message Hannes Dorbath 2007-07-10 07:13:15 Re: Postgres 8.2 binary for ubuntu 6.10?
Previous Message Ron St-Pierre 2007-07-10 06:01:46 Re: Duplicate Unique Key constraint error