Yes, and confirm that the join order for the hash join is correct - that the need for a large amount of memory for the inner relation is legit.
—Sharon
standalone pool memory error (concurrent queries)
Moderator: NorbertKrupa
Re: standalone pool memory error (concurrent queries)
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
Re: standalone pool memory error (concurrent queries)
We can't really add more projections---these tables are huge, and we're already using lots of license "space".
I did use your memorycap suggestion (though I applied it to each session rather than the user). That actually worked. A twist, though, is that one process had 1/4 or so of the amount of data to process (since it's for 2015-03, which obviously isn't a full month yet), yet that query was still running 30 min past the others before I killed the session (because I figured it would just run away).
I think the likeliest explanation is that the lower amount of resources hooked the planner into a less efficient plan.
Is there any way to see what plan is actually used to run a query? (I say "actually" because I've seen entries in vertica.log implying that queries can get "replanned" when run.)
I did use your memorycap suggestion (though I applied it to each session rather than the user). That actually worked. A twist, though, is that one process had 1/4 or so of the amount of data to process (since it's for 2015-03, which obviously isn't a full month yet), yet that query was still running 30 min past the others before I killed the session (because I figured it would just run away).
I think the likeliest explanation is that the lower amount of resources hooked the planner into a less efficient plan.
Is there any way to see what plan is actually used to run a query? (I say "actually" because I've seen entries in vertica.log implying that queries can get "replanned" when run.)
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: standalone pool memory error (concurrent queries)
Additional projections don't count against your license. License is based on the raw data sized. You're just limited by your hardware.dbmsuser7 wrote:We can't really add more projections---these tables are huge, and we're already using lots of license "space".
You can profile the query. Check out the guide in the documentation.dbmsuser7 wrote:Is there any way to see what plan is actually used to run a query? (I say "actually" because I've seen entries in vertica.log implying that queries can get "replanned" when run.)
Checkout vertica.tips for more Vertica resources.
Re: standalone pool memory error (concurrent queries)
You can view the explain plan for any query that has already executed in the dc_explain_plans table - or through MC though I haven’t had a lot of luck with MC in general just yet. Then you can see the actual explain plan used, rather than doing EXPLAIN today and hoping they are the same.
But you won’t see a difference in the plan for a retried query
If you want to be able to view historical explain plans in general, you’ll probably want to bump up the retention for dc_explain_plans.
—Sharon
But you won’t see a difference in the plan for a retried query
If you want to be able to view historical explain plans in general, you’ll probably want to bump up the retention for dc_explain_plans.
—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC