Question: We have a set of data in 2 tables (tbl_A

Question

Question: We have a set of data in 2 tables (tbl_A

or tbl_B).
One is always CURRENT and one is available to build new/fresher data.

In the past, we would simply change the VIEW to point to the FRESH table if it built successfully.
These are Large tables with MANY MILLIONS of rows.
They are nicely indexed and work well.

What is the recommended Approach in PG. Probably NOT changing the view definition.
We were considering 3 choices (all using a CONTROL_TABLE indicating A or B).

1) Write a Function that does a RETURN QUERY select * from tbl_A; — Or of CONTROL says B, return SELECT from tbl_B;
2) Use a UNION ALL, knowing that ONE HALF of the UNION will ALWAYS be empty.
select tbl.* from CONTROL c, tbl_A tbl where CONTROL.ActiveTable='A'
UNION ALL
select tbl.* from CONTROL c, tbl_B tbl where CONTROL.ActiveTable='B'
3) An INSTEAD OF function, which is a more complicated version of #1 (one extra step)

What is easier for PG and likely best for performance/clarity? (We like #2) If the optimizer is smart enough to see ONLY one side can be true, then it should CLEAVE the entire other path, correct?

#programming #russian

0

22.09.2022

1 ответов

25 просмотров

Yaroslav Schekin · Accepted Answer

> Probably NOT changing the view definition. Why, though? What downsides do you see in the solution? At first glance, it's only: a DDL lock (which could block queries it if there are a lot of long, concurrent transactions during the time it's run). > 1) Write a Function that does a RETURN QUERY select * from tbl_A; — Or of CONTROL says B, return SELECT from tbl_B; Well, only if it is SQL language, inlinable SRF (see https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions ), otherwise performance would be atrocious... but then it's about the same as the next solution. > 2) Use a UNION ALL, knowing that ONE HALF of the UNION will ALWAYS be empty. I'd check if the query plans would be acceptable in this case, if I were you. > 3) An INSTEAD OF function, which is a more complicated version of #1 (one extra step) I'm not sure what do you mean here. PostgreSQL has INSTEAD OF triggers, but those doesn't seem to be relevant, here. > If the optimizer is smart enough to see ONLY one side can be true Yeah, if. ;) I.e. you'd better test it on non-trivial queries.

169 похожих чатов

Question: We have a set of data in 2 tables (tbl_A

1 ответов

Похожие вопросы