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