Обсуждение: Fastest way to join materalized view to child tables

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

On Jul 9, 2007, at 10:07 PM, Postgres User wrote:
> 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

Build an array of IDs and then use that in your 3 queries with the
ANY operator.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)