Fastest way 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: Fastest way to join materalized view to child tables
Date: 2007-07-10 03:07:51
Message-ID: b88c3460707092007m62dbada5td8b021ba34af7171@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...

Responses

Browse pgsql-general by date

  From Date Subject
Next Message alexander lunyov 2007-07-10 04:40:24 Re: russian case-insensitive regexp search not working
Previous Message novnov 2007-07-10 01:09:45 Postgres 8.2 binary for ubuntu 6.10?