Hello, Hansu Chung,
I’m sorry for the difficulties you’ve having with PostgreSQL. There is a known behavior with common table expressions (CTE) prior to version 12 where the query in the CTE is materialized as a separate, un-indexed table.
However, things have improved with version 12 and 13: common table expressions are optimized together with the rest of the query such that indexes and other query optimizations can be applied. This is when the CTE is NOT MATERIALIZED. You can read about it here: https://www.postgresql.org/docs/12/queries-with.html.
Do you have the option of working on PostgreSQL 13.0?
-Chris