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...
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 |