Re: [PERFORM] Optimize complex join to use where condition before

2005-05-13 Thread John A Meinel
Greg Stark wrote: Sebastian Hennebrueder [EMAIL PROTECTED] writes: User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) ... Nested Loop (cost=1349.13..1435.29 rows=1 width=2541) (actual time=1640.000..3687.000 rows=62 loops=1) Join Filter: (inner.fid = outer.faufgaben_id) - Index Scan

Re: [PERFORM] Optimize complex join to use where condition before

2005-05-13 Thread Sebastian Hennebrueder
I found a solution to improve my query. I do not know why but the statistics for all column has been 0. I changed this to 10 for index columns and to 20 for all foreign key columns. and to 100 for foreign key columns. I set the random page cost to 2 and now the query runs as expected. Many

Re: [PERFORM] Optimize complex join to use where condition before

2005-05-13 Thread John Arbash Meinel
Sebastian Hennebrueder wrote: I found a solution to improve my query. I do not know why but the statistics for all column has been 0. I changed this to 10 for index columns and to 20 for all foreign key columns. and to 100 for foreign key columns. I set the random page cost to 2 and now

Re: [PERFORM] Optimize complex join to use where condition before

2005-05-12 Thread Sebastian Hennebrueder
Solution to my problem. I added indexes to each foreign_key (there had been some missing). I will try tomorrow by daylight what influence this had actually. Only the indexes did not change anything! Even with lower random_page_costs and higher shared mem. The big change was the following I created

Re: [PERFORM] Optimize complex join to use where condition before

2005-05-12 Thread Sebastian Hennebrueder
Solution not found as I thought. I integrated the query in a view and the query plan became very bad once again. The reason is that when I am using the view I have the joins in a differerent order. Does anyone have an idea to solve this. Sebastian a) bad order but the one I have in my

Re: [PERFORM] Optimize complex join to use where condition before

2005-05-12 Thread Greg Stark
Sebastian Hennebrueder [EMAIL PROTECTED] writes: User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) ... Nested Loop (cost=1349.13..1435.29 rows=1 width=2541) (actual time=1640.000..3687.000 rows=62 loops=1) Join Filter: (inner.fid = outer.faufgaben_id) - Index Scan using