The Vertica Database Forums

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

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.)

dbmsuser7 wrote:We can't really add more projections---these tables are huge, and we're already using lots of license "space".

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: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.)

You can profile the query. Check out the guide in the documentation.

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