By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,728 Members | 1,072 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,728 IT Pros & Developers. It's quick & easy.

Optimize query

P: n/a
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)

Nov 23 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.