If two PostgreSQL table has gist index, can their union be considered as an indexed table?-CodePudding

I have three tables, table_a, table_b, table_c. All of them has gist index. I would like to perform a left join between table_c and the UNION of table_a and table_b. Can the UNION be considered "indexed"? I assume it would better to create new table as the UNION, but these tables are huge so I try to avoid this kind of redundancy.

In terms of SQL, my question:

Is this

SELECT * FROM myschema.table_c AS a
LEFT JOIN
(SELECT col_1,col_2,the_geom FROM myschema.table_a
UNION
SELECT col_1,col_2,the_geom FROM myschema.table_b) AS b
ON ST_Intersects(a.the_geom,b.the_geom);

equal to this?

CREATE TABLE myschema.table_d AS
SELECT col_1,col_2,the_geom FROM myschema.table_a
UNION
SELECT col_1,col_2,the_geom FROM myschema.table_b;

CREATE INDEX idx_table_d_the_geom
ON myschema.table_d USING gist
(the_geom)
TABLESPACE mydb;

SELECT * FROM myschema.table_c AS a
LEFT JOIN myschema.table_d AS b
ON ST_Intersects(a.the_geom,b.the_geom);

CodePudding user response：

You can look at the execution plan with EXPLAIN, but I doubt that it will use the indexes.

Rather than performing a left join between one table and the union of three other tables, you should perform the union of the left joins between the one table and each of the three tables in turn. That will be a longer statement, but PostgreSQL will be sure to use the index if that can speed up the left joins.

Be sure to use UNION ALL rather than UNION unless you really have to remove duplicates.