Thread: Poor performance on an aggregate query

Poor performance on an aggregate query

From
Anne Rosset
Date:

Hi all,

I have this simple query that has performance issue.  I am assuming there is something wrong in our configuration.  Can someone point me to the right direction? (our work_mem is set to 64MB)

 

srdb=> explain analyze 013-04-15 16:51:20,223 INFO  [com.vasoftware.sf.server.common.querygenerator.Query] (http--127.0.0.1-8080-11) Query: [Id: 4652] [duration: 51999][debug sql] SELECT

psrdb(>        MAX(length(discussion_post.id)) AS maxLength

psrdb(> FROM

psrdb(>        discussion_post discussion_post^C

psrdb=> explain analyze SELECT

psrdb->        MAX(length(discussion_post.id)) AS maxLength

psrdb-> FROM

psrdb->        discussion_post discussion_post

psrdb-> ;

                                                                    QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------

Aggregate  (cost=10000043105.13..10000043105.14 rows=1 width=10) (actual time=52150.015..52150.015 rows=1 loops=1)

   ->  Seq Scan on discussion_post  (cost=10000000000.00..10000041980.90 rows=449690 width=10) (actual time=0.006..51981.746 rows=449604 loops=1)

Total runtime: 52150.073 ms

(3 rows)

 

Thanks a lot,

Anne

Re: Poor performance on an aggregate query

From
Peter Geoghegan
Date:
On Mon, Apr 15, 2013 at 1:59 PM, Anne Rosset <arosset@collab.net> wrote:
> I have this simple query that has performance issue.  I am assuming there is
> something wrong in our configuration.  Can someone point me to the right
> direction? (our work_mem is set to 64MB)

Why don't you try creating a functional index on length(discussion_post.id)?


--
Regards,
Peter Geoghegan