Optimize query

[Michal Hlavac]

Hello,

I have one main table and 2 many-to-many relation tables and 2
one-to-many relation table. Main table has 150 000 rows and many-to-many
tables have about 300 000 rows.

I am creating web portal.
In my query I must call distinct with many-to-many relation. But this
query is too slow for web application. This query costs 8,5 seconds.
For example:

SELECT distinct(i_part_id), i_is_special, a.v_name, d.v_company,
f_price, t_description
FROM v_part a
JOIN l_model_to_part b USING (i_part_id)
JOIN l_part_to_cat c USING (i_part_id)
JOIN k_seller d USING (i_seller_id)
JOIN c_part_cat e USING (i_part_cat_id)
WHERE TRUE AND condition ORDER BY i_is_special DESC, v_name;

So, I created another way, how to grow up performace. I created
temporary table with result of query, which has only one column (id of
product). And finally I do a join of temporary table with main table.
This query costs 0,5 seconds with everything.

It is 8,5 seconds versus 0,5 seconds...

Is this the right way, how to increase the performance of the queries???
I know, that every problem is specific. I mean this philosophical.

thanx, miso

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)