Can't optimize query

[asterite]

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]

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

[pborne]

Make sure you have foreign keys and primary keys defined.