Date: Sun, 1 Oct 2017 13:35:47 +0000 (UTC)
From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
Cc: pgsql-performance@postgresql.org
Message-ID: <58852558.43813.1506864947581.JavaMail.zimbra@fmed.uba.ar>
In-Reply-To: 
 <CA+t6e1mVtJveyoRRW8fLzY0tJhXntLrYxpSrk0=dDH8q93VPEA@mail.gmail.com>
References: 
 <CA+t6e1mVtJveyoRRW8fLzY0tJhXntLrYxpSrk0=dDH8q93VPEA@mail.gmail.com>
Subject: Re: select with max functions
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Thread-Topic: select with max functions
Thread-Index: ZfZLrn6WKNXkBXvHtdWrC1zT1vlSfg==
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


----- Mensaje original -----
> De: "Mariel Cherkassky" <mariel.cherkassky@gmail.com>
> Para: pgsql-performance@postgresql.org
> Enviados: Domingo, 1 de Octubre 2017 9:41:37
> Asunto: [PERFORM] select with max functions
> 
> Hi,
> I need to use the max function in my query. I had very bad performance when
> I used the max  :
> 
>                SELECT Ma.User_Id,
>                       COUNT(*) COUNT
>                FROM   Manuim Ma
>                WHERE  Ma.Bb_Open_Date  =
>                                   (SELECT max(Bb_Open_Date)
>                                    FROM   Manuim Man
>                                    WHERE  Man.User_Id = Ma.User_Id
>                                   )
>                GROUP  BY Ma.User_Id
>                HAVING COUNT(*) > 1;
> 
> 
>                                                QUERY PLAN
> 
> ---------------------------------------------------------------------------------------------------------
>  GroupAggregate  (cost=0.56..3250554784.13 rows=115111 width=18)
>    Group Key: ma.user_id
>    Filter: (count(*) > 1)
>    ->  Index Scan using manuim_i_user_id on manuim ma
> (cost=0.56..3250552295.59 rows=178324 width=10)
>          Filter: (bb_open_date = (SubPlan 1))
>          SubPlan 1
>            ->  Aggregate  (cost=90.98..90.99 rows=1 width=8)
>                  ->  Index Scan using manuim_i_user_id on manuim man
> (cost=0.56..90.92 rows=22 width=8)
>                        Index Cond: ((user_id)::text = (ma.user_id)::text)
> (9 rows)
> 
> 
> 
> So I used the limit 1 option :
> 
>                SELECT Ma.User_Id,
>                       COUNT(*) COUNT
>                FROM   Manuim Ma
>                WHERE  Ma.Bb_Open_Date  =
>                                   (SELECT Bb_Open_Date
>                                    FROM   Manuim Man
>                                    WHERE  Man.User_Id = Ma.User_Id order
> by                                   bb_open_date desc limit 1
>                                   )
>                GROUP  BY Ma.User_Id
>                HAVING COUNT(*) > 1;
> 
> and the performance are still the same :
> 
>                                                   QUERY PLAN
> 
> ---------------------------------------------------------------------------------------------------------------
>  GroupAggregate  (cost=0.56..3252248863.46 rows=115111 width=18)
>    Group Key: ma.user_id
>    Filter: (count(*) > 1)
>    ->  Index Scan using manuim_i_user_id on manuim ma
> (cost=0.56..3252246374.92 rows=178324 width=10)
>          Filter: (bb_open_date = (SubPlan 1))
>          SubPlan 1
>            ->  Limit  (cost=91.03..91.03 rows=1 width=8)
>                  ->  Sort  (cost=91.03..91.09 rows=22 width=8)
>                        Sort Key: man.bb_open_date DESC
>                        ->  Index Scan using manuim_i_user_id on manuim man
> (cost=0.56..90.92 rows=22 width=8)
>                              Index Cond: ((user_id)::text =
> (ma.user_id)::text)
> (11 rows)
> 
> 
> 
> the reading on the table manuim takes a lot of effort, what else can I do ?
> the table`s size is 8G.
> 
> select count(*) from manuim;
>   count
> ----------
>  35664828
> (1 row)
> 
> the indexes on the table :
>  "manuim_bb_open_date" btree (bb_open_date)
> "manuim_i_user_id" btree (user_id)
> 
> 
> Any idea how can I continue from here ? Thanks , Mariel.

Start by posting the results of "explain analyze" of that queries, so we can see some timming stuff.

Gerardo


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance