Fastest way to join materalized view to child tables
От | Postgres User |
---|---|
Тема | Fastest way to join materalized view to child tables |
Дата | |
Msg-id | b88c3460707092007m62dbada5td8b021ba34af7171@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Fastest way to join materalized view to child tables
|
Список | 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...
В списке pgsql-general по дате отправления: