Re: union all taking years - PG 9.6

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Mon, May 15, 2017 at 4:21 PM, Patrick B <patrickbakerbr@xxxxxxxxx> wrote:

Hi guys.

I have two tables, where 'tableA' is the old and 'tableC' is the new one. I say "new/old" because we are migrating the data from tableA to tableC soon.

I created a view selecting from both tables, with a UNION ALL between them. When selecting from that view, it's really slow. I can't even run explain analyze (it's been 1h and query did not finished yet).

​Try just comparing the explain plans.

However, when running both selects with explain analyze, query is fast.

​Do those selects contain where clauses?​

What should I do in this case? Why is that taking so long? I assume it's because the UNION will look for duplicates?

​It won't because you specified "ALL"​

I'll presume there is a where clause involved because concatenating two queries via UNION ALL without either having WHERE clause should be pretty much interchangeable.  Its unclear to me where things stand regarding pushing down WHERE clauses through the UNION ALL which if it cannot for some reason would result in what you are observing.

PostgreSQL version would help too.

David J.