Re: bad query performance - Mailing list pgsql-sql

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