using sequential scan instead of index for join with a union
I am joining the union of three tables with another table. Postgresql uses the index when only two tables are in the union. If I add one more table to the union, it switches to seq scan. Apparently it also uses the index when only one table is joined.
SELECT 'SPEECH' datatype, s.id, mbct_id FROM speech s
SELECT 'NOTICE' datatype, notice.id, mbct_id FROM notice
) x join NBSMultiBroadcast y on x.mbct_id=y.id where y.zhtw_grp_bct between '2019-05-10' and '2019-05-17';
The estimated number of rows is not off against the actual number of rows, which is around 120. So, I don't really understand why PostgreSQL seems to believe it should use Seq Scan due to a relatively large number of rows are expected.
I am using v11.3:
PostgreSQL 11.3 (Ubuntu 11.3-1.pgdg16.04+1) on i686-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 32-bit