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.
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?
Thanks
Patrick.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Mon, May 15, 2017 at 4:21 PM, Patrick B <patrickbakerbr@gmail.com> 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.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general