Only one user took same query longer

rezakomo

Hi, I'm really new in vertica, and I need your help.
I have 4 users with similar resource pool but for different purpose. one is for tableau and one for qliksense.
I dont know why but the qliksense user took longer execution time than tableau user even though with the same query and we both used dbeaver as a database tool. ever happen like this before?

Vertica_Curtis

Are they using DBeaver, or Qliksense/Tableau? I would eliminate the tool, and just run the query in vsql as each user, and see if they are different. That will negate any difference in the tools themselves, and determine if their resource pools are somehow configured differently. Of course, you could just easily inspect the resource pools to see if there are any differences there. Otherwise, the queries should run about the same. You can also check the query history in the QUERY_PROFILES system table. Find the query, and see what the differences are. Are there significant differences in how much data was returned, or the query runtimes? That table will have those details.

rezakomo

Hi @Vertica_Curtis , sorry if I confused you. The 'users' used for qliksense and tableau for the daily use. I use dbeaver to compare the execution time. I will try u'r suggestion first and let u know . Thank u for your response.
Regards,

Vertica_Curtis

Oh, I see, so you have a Qlik and a Tableau user, and each has it's own resource. Signing into DBeaver as each user, you ran the query. I assume this is a copy/paste query, and the query was the same? It's possible something happened in the system that would cause one query to not perform as well, but assuming that's not the case, visually inspect the resource pools for each user to ensure they are the same. Also, make sure each user is assigned to their respective pools by checking the USERS table.

mosheg

In addition, verify both users query the same projections with the same plan.
SHOW SEARCH_PATH;
SELECT CURRENT_SCHEMA;
EXPLAIN your_sql_statement;

rezakomo

Hi @Vertica_Curtis and @mosheg , It turns out there are 218 queries is_executing for 2 days. Any idea how much query is too much?
Thank u for ur replies.
Regards,

Jim_Knicely

What version of Vertica are you running? In older versions of Vertica the QUERY_PROFILES system view sometimes incorrectly returns results when the IS_EXECUTING flag is true. It's reading data from multiple data collector tables (DC_REQUESTS_ISSUED, DC_REQUESTS_COMPLETED).
I always check the SESSIONS system table for "currently" running queries:

SELECT user_name, COUNT(*) FROM sessions WHERE current_statement <> '' AND session_id <> current_session() GROUP BY user_name ORDER BY user_name;

Make sure to run the query as the dbadmin user so that you can see all of the sessions.