Optimize query

From: Michal Hlavac <hlavki(at)medium13(dot)sk>
To: pgsql-general(at)postgresql(dot)org
Subject: Optimize query
Date: 2004-04-05 13:48:29
Message-ID: 407163AD.1060406@medium13.sk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Jakub 2004-04-05 13:54:35 Need to replace PostgreSQL 7.4 with 7.3 on cygwin
Previous Message mike 2004-04-05 11:20:47 Re: Problem restoring Database