Re: Optimizing select count query which often takes over 10 seconds

# explain analyze select count(id) from (
            select id,
                   row_number() over(partition by yw order by money
desc) as ranking
            from pref_money
        ) x
        where x.ranking = 1 and id='OK452217781481';
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=63694.22..63694.23 rows=1 width=82) (actual
time=4520.719..4520.719 rows=1 loops=1)
   ->  Subquery Scan x  (cost=48519.10..63694.19 rows=11 width=82)
(actual time=4470.620..4520.710 rows=6 loops=1)
         Filter: ((x.ranking = 1) AND ((x.id)::text = 'OK452217781481'::text))
         ->  WindowAgg  (cost=48519.10..57190.58 rows=433574 width=26)
(actual time=4293.315..4491.652 rows=429803 loops=1)
               ->  Sort  (cost=48519.10..49603.03 rows=433574
width=26) (actual time=4293.306..4352.544 rows=429803 loops=1)
                     Sort Key: pref_money.yw, pref_money.money
                     Sort Method:  external sort  Disk: 15856kB

                     ->  Seq Scan on pref_money  (cost=0.00..7923.74
rows=433574 width=26) (actual time=0.006..41.907 rows=429803 loops=1)

 Total runtime: 4525.662 ms
(9 rows)

Thank you for any hints
Alex


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