Message-ID: <3EC274C6.6040009@ualg.pt>
Date: Wed, 14 May 2003 17:54:30 +0100
From: Luis Sousa <llsousa@ualg.pt>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.2.1) Gecko/20021226 Debian/1.2.1-9
MIME-Version: 1.0
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Didrik Pinte <dp@adhocsolutions.com>, pgsql-sql@postgresql.org
Subject: Re: bad query performance
References: <20030514084626.O44794-100000@megazone23.bigpanda.com>
In-Reply-To: <20030514084626.O44794-100000@megazone23.bigpanda.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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@postgresql.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