Обсуждение: [GENERAL] union all taking years - PG 9.6
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.On 05/15/2017 04:21 PM, Patrick B 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). > > However, when running both selects with explain analyze, query is fast. > > > What should I do in this case? Why is that taking so long? I assume it's > because the UNION will look for duplicates? Without actual information, the only thing that can be said is that it is slow. Information needed: Postgres version Table schema for tableA and tableC The view query The output of the explain for the view query. > > Thanks > Patrick. -- Adrian Klaver adrian.klaver@aklaver.com
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).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.
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.
On Mon, May 15, 2017 at 6:21 PM, Patrick B <patrickbakerbr@gmail.com> wrote: > 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). Have you ruled out locks? Let the select run and check pg_stat_activity and pg_locks. If everything is good there, please paste contents of explain (without analyze) and explain analyze of the 'good' queries. merlin