Re: select with max functions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



explain analyze   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;
                                                                           QUERY PLAN                                    
                                        
-------------------------------------------------------------------------------------------------------------------------
----------------------------------------
 GroupAggregate  (cost=0.56..2430770384.80 rows=128137 width=18) (actual time=55.823..2970443.757 rows=1213 loops=1)
   Group Key: ma.user_id
   Filter: (count(*) > 1)
   Rows Removed by Filter: 3693020
   ->  Index Scan using manuim_i_user_id on manuim ma  (cost=0.56..2430767766.00 rows=178324 width=10) (actual time=0.249
..2966355.734 rows=3695461 loops=1)
         Filter: (bb_open_date = (SubPlan 1))
         Rows Removed by Filter: 31969367
         SubPlan 1
           ->  Limit  (cost=68.00..68.00 rows=1 width=8) (actual time=0.082..0.082 rows=0 loops=35664828)
                 ->  Sort  (cost=68.00..68.04 rows=16 width=8) (actual time=0.081..0.081 rows=0 loops=35664828)
                       Sort Key: man.bb_open_date DESC
                       Sort Method: quicksort  Memory: 25kB
                       ->  Index Scan using manuim_i_user_id on manuim man  (cost=0.56..67.92 rows=16 width=8) (actual ti
me=0.001..0.069 rows=85 loops=35664828)
                             Index Cond: ((user_id)::text = (ma.user_id)::text)
 Planning time: 0.414 ms
 Execution time: 2970444.732 ms
(16 rows)

2017-10-02 16:45 GMT+03:00 Gerardo Herzig <gherzig@xxxxxxxxxxx>:


----- Mensaje original -----
> De: "Mariel Cherkassky" <mariel.cherkassky@xxxxxxxxx>
> Para: "Andreas Kretschmer" <andreas@xxxxxxxxxxxxxxx>
> Enviados: Lunes, 2 de Octubre 2017 10:25:19
> Asunto: Re: select with max functions
>
> Andreas I tried to rewrite it with the function rank() but I failed. The
> query you wrote isnt the same as what I search. Moreover, I cant use
> explain analyze because it is taking to much time to run and I'm getting
> timeout..
>
> 2017-10-01 21:48 GMT+03:00 Andreas Kretschmer <andreas@xxxxxxxxxxxxxxx>:

Do a "set statement_timeout TO 0" prior to "explain analyze"


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux