Slow search of minimum in list of maximums with sql with-clause

Question

I have question about this query, a little bit slower for me :(

My problem : I'm searching the minimum ticks in a list which contains each maximum of each list of ticks by seed.

=> Equal to min( max(seed1), max(seed2), max(seed 3), etc. )

PS : Each seed contains ~ 10000 rows (1 row = 1 ticks) PS2 : I have one table with contains all data ( it's not a good solution, i know, but i have no choice at this time of my project). Simulation contains millions rows, hum.. like 40 M :/ PS3 : I had index on v_seed and v_exp.

Time for execute this query is ~ 135 seconds , it's very slow :(

 WITH summary AS ( SELECT v_ticks,v_seed, ROW_NUMBER() OVER(PARTITION BY v_seed 
 ORDER BY v_ticks DESC) as rk  FROM simulations 
 WHERE v_exp=23 AND v_seed IN (2133836778, -2061794068, 1260042744, -1324330098, -423279216, -685846464, 142959438, -1154715079, 1062336798,-624140595, -922352011, -613647601, -330177159, 1945002173, 131053356, -216538235, -636982783, 979930868, 321237028, -1103129161, 476235295, -1916604834, -54027108, 17850135, -60658084) ) 
 SELECT min(s.v_ticks)
 FROM summary s WHERE s.rk = 1

UPDATE 1 : EXPLAIN INFORMATION

Aggregate  (cost=6327697.46..6327697.47 rows=1 width=4)
  CTE summary
    ->  WindowAgg  (cost=5302458.61..5784782.09 rows=24116174 width=12)
         ->  Sort  (cost=5302458.61..5362749.05 rows=24116174 width=12)
                Sort Key: simulations.v_seed, simulations.v_ticks
                ->  Bitmap Heap Scan on simulations  (cost=415238.16..1933251.42 rows=24116174 width=12)
                      Recheck Cond: (v_seed = ANY ('{2133836778,-2061794068,1260042744,-1324330098,-423279216,-685846464,142959438,-1154715079,1062336798,-624140595,-922352011,-613647601,-330177159,1945002173,131053356,-216538235,-636982783,979930868,321237028,-1103129161,476235295,-1916604834,-54027108,17850135,-60658084}'::bigint[]))
                      Filter: (v_exp = 23)"
                          ->  Bitmap Index Scan on index_seed  (cost=0.00..409209.12 rows=25752303 width=0)
                            Index Cond: (v_seed = ANY ('{2133836778,-2061794068,1260042744,-1324330098,-423279216,-685846464,142959438,-1154715079,1062336798,-624140595,-922352011,-613647601,-330177159,1945002173,131053356,-216538235,-636982783,979930868,321237028,-1103129161,476235295,-1916604834,-54027108,17850135,-60658084}'::bigint[]))
  ->  CTE Scan on summary s  (cost=0.00..542613.92 rows=120581 width=4)
        Filter: (rk = 1)

If you have an idea to optimize this query, it's cool :) Thx !

Answer 1

Make sure the table Simulations has a clustered index on v_seed and try this:

CREATE TABLE #Seeds(seed int)

INSERT #Seeds
SELECT  2133836778 UNION
SELECT  -2061794068 UNION
...
 SELECT -60658084

WITH summary AS
( 
SELECT v_ticks,v_seed, ROW_NUMBER() OVER(PARTITION BY v_seed ORDER BY v_ticks DESC) as rk  
FROM    simulations sim
    INNER JOIN
        #Seeds seeds
    ON sim.v_seed = seeds.seed

WHERE   v_exp   =23 
 ) 
SELECT  min(s.v_ticks)
FROM    summary s 
WHERE   s.rk = 1

Answer 2

Is it not the same as

WITH summary AS( 
SELECT v_seed, MAX(v_ticks)
FROM simulations 
WHERE v_exp=23 AND v_seed IN (2133836778, -2061794068, 1260042744, -1324330098, -423279216, -685846464, 142959438, -1154715079, 1062336798,-624140595, -922352011, -613647601, -330177159, 1945002173, 131053356, -216538235, -636982783, 979930868, 321237028, -1103129161, 476235295, -1916604834, -54027108, 17850135, -60658084) )
GROUP BY v_seed)
SELECT MIN(v_ticks)

Answer 3

This should do the trick (sorry, no postgres db to test on, but it works on mysql). Be sure that you have an index on v_seed, and v_exp as both rows are used to filter the data at some point.

select min(s.max_tick) as smallest_max
from (select max(v_ticks) as max_tick
      from wts_test
      where v_exp=23 AND
            v_seed IN (2133836778, -2061794068, 1260042744, -1324330098, -423279216, -685846464, 142959438, -1154715079, 1062336798,-624140595, -922352011, -613647601, -330177159, 1945002173, 131053356, -216538235, -636982783, 979930868, 321237028, -1103129161, 476235295, -1916604834, -54027108, 17850135, -60658084)
  group by v_seed) s