Re: bad query performance

Stephan Szabo wrote:

>On Mon, 12 May 2003, Didrik Pinte wrote:
>
>
>
>>Here is the query :
>>------------------------------------------------------------------------------------
>>SELECT profiles_et.username, profiles_et.name, profiles_et.firstname,
>>profiles_et.email, company_et.name AS company, count(logs_et.dt) AS cnt,
>>pro_invoice_addr_at.id
>>
>>
>>FROM
>>(
>> (
>> (
>> secu.company_et JOIN secu.pro_invoice_addr_at
>>ON (pro_invoice_addr_at.company = company_et.id)
>> )
>>
>> JOIN secu.profiles_et ON (pro_invoice_addr_at.profile =
>>profiles_et.id)
>> )
>> JOIN secu.logs_et ON (logs_et.invaddr = pro_invoice_addr_at.id)
>>)
>>
>>
>> GROUP BY profiles_et.username, profiles_et.name, profiles_et.firstname,
>>profiles_et.email, company_et.name, pro_invoice_addr_at.id
>>
>>ORDER BY count(logs_et.dt) DESC;
>>------------------------------------------------------------------------------------
>>
>>
>>
>>The secu_company, secu_prov_invoice_addr_at and secu_profiles contains only
>>informations about the users.
>>
>>The query is executing in 8,6 minutes.... Indexes are defined on all the
>>field used in the different joins.
>>
>>
>
>Have you recently analyzed the tables involved?
>I'd guess that the two outer sorts and the sort on logs_et are probably
>the killers, but can you send explain analyze output? Looking at the
>estimated row numbers and width, it seems to me that with 16Mb of sort_mem
>it's going to have to sort on disk (although you don't have enough memory
>to up it far enough probably if the estimates are right)
>
>
>
>
>>Here is the query plan :
>>
>>------------------------------------------------------------------------------------
>>Sort (cost=895649.54..896073.23 rows=169474 width=145)
>> Sort Key: count(logs_et.dt)
>> -> Aggregate (cost=831240.24..865135.10 rows=169474 width=145)
>> -> Group (cost=831240.24..860898.24 rows=1694743 width=145)
>> -> Sort (cost=831240.24..835477.10 rows=1694743 width=145)
>> Sort Key: profiles_et.username, profiles_et.name,
>>profiles_et.firstname, profiles_et.email, company_et.name,
>>pro_invoice_addr_at.id
>> -> Merge Join (cost=274406.73..304066.75
>>rows=1694743 width=145)
>> Merge Cond: ("outer".id = "inner".invaddr)
>> -> Sort (cost=143.81..144.92 rows=446 width=126)
>> Sort Key: pro_invoice_addr_at.id
>> -> Merge Join (cost=90.27..124.18
>>rows=446 width=126)
>> Merge Cond: ("outer".id =
>>"inner".profile)
>> -> Index Scan using profiles_pk on
>>profiles_et (cost=0.00..24.98 rows=449 width=66)
>> -> Sort (cost=90.27..91.39
>>rows=446 width=60)
>> Sort Key:
>>pro_invoice_addr_at.profile
>> -> Merge
>>Join (cost=37.82..70.65 rows=446 width=60)
>> Merge Cond:
>>("outer".company = "inner".id)
>> -> Index Scan using
>>invaddr_at_company_idx on pro_invoice_addr_at (cost=0.00..24.68 rows=446
>>width=33)
>> -> Sort
>>(cost=37.82..38.55 rows=291 width=27)
>> Sort Key:
>>company_et.id
>> -> Seq Scan on
>>company_et (cost=0.00..25.91 rows=291 width=27)
>> -> Sort (cost=274262.92..278499.78
>>rows=1694743 width=19)
>> Sort Key: logs_et.invaddr
>> -> Seq Scan on
>>logs_et (cost=0.00..55404.43 rows=1694743 width=19)
>>------------------------------------------------------------------------------------
>>
>>The computer is a Pentium III 850 Mhz with 256 mb RAM (Sort memory for
>>postres is 16 mb, and 64 mb of shared memory).
>>
>>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>
>
>
>
-> Seq Scan on
company_et (cost=0.00..25.91 rows=291 width=27)
-> Sort (cost=274262.92..278499.78
rows=1694743 width=19)
Sort Key: logs_et.invaddr

Do you have any indexes defined for logs_et.invaddr and for
pro_invoice_addr_at.id ?

Luis Sousa