Administrace komentářů

Tedy smekam.

Nebudu zastirat, ze vubec postupu nerozumim.

Na mych datech to dela 373.236 ms, coz je vyrazne zlepseni.

Ale uvazoval jsem:

graphs=# EXPLAIN (BUFFERS, ANALYZE)select server.id as ser,vanview.id as van from server,vanview where label like 'nom%' ;
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2.49 rows=24 width=8) (actual time=0.018..0.031 rows=24 loops=1)
   Buffers: shared hit=2
   ->  Seq Scan on server  (cost=0.00..1.15 rows=8 width=4) (actual time=0.010..0.011 rows=8 loops=1)
         Filter: (label ~~ 'nom%'::text)
         Rows Removed by Filter: 4
         Buffers: shared hit=1
   ->  Materialize  (cost=0.00..1.04 rows=3 width=4) (actual time=0.001..0.001 rows=3 loops=8)
         Buffers: shared hit=1
         ->  Seq Scan on vanview  (cost=0.00..1.03 rows=3 width=4) (actual time=0.003..0.006 rows=3 loops=1)
               Buffers: shared hit=1
 Total runtime: 0.063 ms
(11 rows)

graphs=# EXPLAIN (BUFFERS, ANALYZE)SELECT max(datum),1,1 FROM van WHERE server=1 AND vanview=1;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=4.15..4.16 rows=1 width=0) (actual time=4.946..4.946 rows=1 loops=1)
   Buffers: shared hit=559
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..4.15 rows=1 width=8) (actual time=4.941..4.942 rows=1 loops=1)
           Buffers: shared hit=559
           ->  Index Only Scan Backward using van_pkey on van  (cost=0.00..1854269.90 rows=447277 width=8) (actual time=4.939..4.939 rows=1 loops=1)
                 Index Cond: ((datum IS NOT NULL) AND (server = 1) AND (vanview = 1))
                 Heap Fetches: 1
                 Buffers: shared hit=559
 Total runtime: 4.985 ms
(10 rows)

graphs=#

4.985*24+0.063=119.703 ms, takze kdybych to spustil v hloupem loopu z aplikace, jsem na tom lepe.

tak jsem napsal:

CREATE OR REPLACE FUNCTION max1 ()
RETURNS TABLE ( max timestamp,
s integer,
v integer)
AS $$
DECLARE row record;
BEGIN
FOR row IN SELECT server.id AS ser,vanview.id AS van FROM server,vanview WHERE label LIKE 'nom%' LOOP

 RETURN QUERY SELECT
         max(datum),row.ser,row.van FROM van WHERE server=row.ser AND vanview=row.van;
END LOOP;



END; $$
LANGUAGE 'plpgsql';

to kdyz spustim:

EXPLAIN (BUFFERS, ANALYZE)select * from max1();
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Function Scan on max1  (cost=0.25..10.25 rows=1000 width=16) (actual time=27.605..27.606 rows=24 loops=1)
   Buffers: shared hit=3422
 Total runtime: 27.625 ms
(3 rows)

Tato rychlost je pro mne dostatecna.

Ted si projdu jeste nekolikrat Vase reseni, snad to nakonec pochopim.

dekuji za inspiraci

marek

ps: stejne je skoda, ze si to postgres nenaplanuje podobne, jako ta funkce...