Re: bad query performance

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).