Can't optimize query

Moderator: NorbertKrupa

Post Reply
Posts: 5
Joined: Wed May 08, 2013 9:02 pm

Can't optimize query

Post by asterite » Thu May 16, 2013 8:38 pm

Hi all,

We have a table with 600000+ rows. Here's the query we need to perform (the actual tables don't matter, it's five joined tables and an order by):

SELECT test_results.*, AS instrument_name, AS instrument_model_name, AS laboratory_name, AS laboratory_country, AS laboratory_city, laboratories.zip_code AS laboratory_zip_code, laboratories.location_id AS laboratory_location_id, AS institution_name FROM test_results JOIN instruments ON = test_results.instrument_id JOIN instrument_models ON = instruments.instrument_model_id JOIN laboratories ON = instruments.laboratory_id JOIN institutions ON = instruments.institution_id ORDER BY source asc LIMIT 25;

We let the database designer analyze that query. Yet, the query always takes about 1 second to finish.

If we remove the "ORDER BY" clause it finishes in 50ms.

The projection is listed as "is pre join" false and "up to date" true.

We tried creating a projection useing "create projection foo as select ...". We see the projection was created and listed as "is pre join" true and "up to date" false (even though we run "select start_refresh()"). Still, the query takes 1 second to finish.

Are we doing something wrong? How can we make this query be faster?


Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Can't optimize query

Post by scutter » Sun May 19, 2013 3:11 pm

If your new projection is still listed as not up to date, check the PROJECTION_REFRESHES table to see why it didn't refresh. Or try "select refresh()" on it and see what error message you get. Also once it's refreshed, verify that it's being used by reviewing the EXPLAIN output for your query to see which projection is has decided to use.

Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

Posts: 20
Joined: Mon Feb 18, 2013 1:37 am

Re: Can't optimize query

Post by pborne » Fri Jan 03, 2014 6:09 pm

Make sure you have foreign keys and primary keys defined.

Post Reply

Return to “Vertica Performance Tuning”