Can't optimize query

Moderator: NorbertKrupa

Post Reply
asterite
Newbie
Newbie
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.*, instruments.name AS instrument_name, instrument_models.name AS instrument_model_name, laboratories.name AS laboratory_name, laboratories.country AS laboratory_country, laboratories.city AS laboratory_city, laboratories.zip_code AS laboratory_zip_code, laboratories.location_id AS laboratory_location_id, institutions.name AS institution_name FROM test_results JOIN instruments ON instruments.id = test_results.instrument_id JOIN instrument_models ON instrument_models.id = instruments.instrument_model_id JOIN laboratories ON laboratories.id = instruments.laboratory_id JOIN institutions ON institutions.id = 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?

Thanks,
Ary

scutter
Master
Master
Posts: 301
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
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

pborne
Newbie
Newbie
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”