abclinuxu.cz AbcLinuxu.cz itbiz.cz ITBiz.cz HDmag.cz HDmag.cz abcprace.cz AbcPráce.cz
AbcLinuxu hledá autory!
Inzerujte na AbcPráce.cz od 950 Kč
Rozšířené hledání
×
včera 19:55 | Nová verze

Po dvou letech od vydání verze 3.0 byla vydána nová major verze 4.0 nástrojů LXC, LXD a LXCFS pro kontejnerovou virtualizaci LXC (LinuX Containers). Jedná se o verzi s dlouhodobou podporou (LTS). Ta končí v červnu 2025. Přehled novinek v jednotlivých oznámeních o vydání: LXC, LXD a LXCFS.

Ladislav Hagara | Komentářů: 2
včera 16:11 | Humor

Řada firem své letošní již připravené aprílové žertíky kvůli SARS-CoV-2 a COVID-19 nezveřejnila. Přehled zveřejněných například na April Fools' Day On The Web. Na CoinMarketCapu byla přidána nová kryptoměna: toaleťáky. Ve hře World of Tanks jsou vylepšené tanky, v PUBG nový herní mód Fantasy Battle Royale, …

Ladislav Hagara | Komentářů: 3
včera 15:22 | Zajímavý projekt

Komunity KDE a GNOME, které doposud vyvíjely příslušná desktopová prostředí, se rozhodly přestat tříštit síly a představují společný projekt KNOME, který nabídne konfigurovatelnost GNOME a jednoduchost KDE v jednom balíčku. Staví na technologiích QTK3 a Kutter.

Fluttershy, yay! | Komentářů: 22
včera 14:11 | Nová verze

Tradičně na apríla byla vydána nová stabilní verze OpenTTD (Wikipedie), tj. open source klonu hry Transport Tycoon Deluxe. Přehled novinek v nejnovější verzi 1.10.0 v seznamu změn. Starší verzi OpenTTD lze vyzkoušet také v prohlížeči.

Ladislav Hagara | Komentářů: 0
včera 06:00 | Nová verze

Po čtyřech a půl měsících vývoje od vydání verze 5.3 byla vydána nová verze 5.4 svobodného open source redakčního systému WordPress. Kódové označení Adderley bylo vybráno na počest amerického jazzového trumpetisty Nata Adderleyho.

Ladislav Hagara | Komentářů: 0
31.3. 23:44 | IT novinky

Association for Computing Machinery vzhledem k probíhající pandemii COVID-19 nabízí bezplatný přístup do databáze publikací ACM Digital Library, a to do 30. června 2020.

Fluttershy, yay! | Komentářů: 2
31.3. 23:11 | IT novinky

Humble Bundle nabízí balík her (některých multiplatformních a/nebo bez DRM), knih, komiksů,… za cenu alespoň €28. Akce Humble Conquer COVID-19 Bundle probíhá do 7. dubna. Výtěžek bude věnován humanitárním/charitativním organizacím Lékaři bez hranic, Direct Relief, International Rescue Committee a Partners In Health.

Fluttershy, yay! | Komentářů: 7
31.3. 18:44 | Komunita

Český LibreOffice tým vydává překlad příručky LibreOffice Online. Příručka vznikla překladem anglického originálu, který byl vytvořen v rámci projektu Google Season of Docs 2019. Příručka je ke stažení na českých stránkách LibreOffice. Český tým pokračuje s překladem příručky Začínáme s LibreOffice a hledá další dobrovolníky pro překlad z angličtiny a revize přeloženého textu.

Zdeněk Crhonek | Komentářů: 0
31.3. 17:55 | Nová verze

Theia je nové modulární vývojové prostředí (IDE) určené k běhu jako webová aplikace a modifikovatelné pomocí doplňků kompatibilních s MS Visual Studio Code. Vývoj zaštiťuje Eclipse Foundation. Více v oznámení vydání verze 1.0.

Fluttershy, yay! | Komentářů: 1
31.3. 17:44 | Upozornění

V souvislosti s nedávnými kybernetickými útoky na nemocniční zařízení v České republice nabídl Národní úřad pro kybernetickou a informační bezpečnost (NÚKIB) ve spolupráci se sdružením CZ.NIC, správcem české národní domény a provozovatelem Národního bezpečnostního týmu CSIRT.CZ, pomoc klíčovým zdravotnickým subjektům, na které se vztahuje reaktivní opatření NÚKIB.

Ladislav Hagara | Komentářů: 2
Chodíte do práce?
 (12%)
 (0%)
 (6%)
 (0%)
 (61%)
 (21%)
 (0%)
Celkem 33 hlasů
 Komentářů: 2, poslední dnes 00:16
Rozcestník

www.AutoDoc.Cz

Dotaz: postgres optimalizace dotazu

12.3.2019 10:26 marek
postgres optimalizace dotazu
Přečteno: 796×

Dobry den

Prosim o nasmerovani, jak zrychlit dotaz:

explain analyze select max(rodatum),server,vanview  from van group by server,vanview;
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=299861.68..299861.92 rows=24 width=16) (actual time=4396.250..4396.256 rows=24 loops=1)
   ->  Seq Scan on van  (cost=0.00..238237.96 rows=8216496 width=16) (actual time=10.354..1658.701 rows=8216067 loops=1)
 Total runtime: 4396.330 ms
(3 rows)

pro tabulku:


\d van
                          Table "public.van"
           Column            |            Type             | Modifiers 
-----------------------------+-----------------------------+-----------
 datum                       | timestamp without time zone | not null
 rodatum                     | timestamp without time zone | 
 server                      | integer                     | not null
 vanview                     | integer                     | not null
 queries                     | bigint                      | 
 lookups                     | bigint                      | 
 proactive-lookups           | bigint                      | 
 ignored-referral-lookups    | bigint                      | 
 cache-misses                | bigint                      | 
 id-spoofing-defense-queries | bigint                      | 
 requests-sent               | bigint                      | 
 tcp-requests-sent           | bigint                      | 
 rate-limited-requests       | bigint                      | 
 noerror                     | bigint                      | 
 servfail                    | bigint                      | 
 nxdomain                    | bigint                      | 
 notimp                      | bigint                      | 
Indexes:
    "van_pkey" PRIMARY KEY, btree (datum, server, vanview)
    "van_datum_idx" btree (datum)
    "van_datum_server_idx" btree (datum, server)
    "van_rodatum_idx" btree (rodatum)
    "van_server_idx" btree (server)
    "van_server_vanview_idx" btree (server, vanview)
    "van_vanview_idx" btree (vanview)
Foreign-key constraints:
    "van_server_fkey" FOREIGN KEY (server) REFERENCES server(id)
    "van_vanview_fkey" FOREIGN KEY (vanview) REFERENCES vanview(id)

postupne jsem pridaval indexy, spoustel VACUUM FULL ANALYZE; ....

Stale mi to prijde priserne pomale.

dekuji

marek

Odpovědi

12.3.2019 13:26 EtDirloth | skóre: 10
Rozbalit Rozbalit vše Re: postgres optimalizace dotazu
Velmi pekna uloha!

Postgresql zial nevie efektivne pouzit index pre group by - pouzije index only scan iba ked sa mu zakaze seq. scan.

Najprv definica tabulky, a simulacia tvojich dat: (testovane na pg11)
--DROP TABLE IF EXISTS van;
CREATE TABLE van (
   rodatum timestamp
 , server  integer NOT NULL
 , vanview integer NOT NULL
);
-- populate with 10M of records with 25 distinct combinations of server & vanview
INSERT INTO van (server, vanview, rodatum)
   SELECT (random() * 4)::int
        , (random() * 4+5)::int
        , ts + ((random() * 5000)::int || 'seconds')::interval
      FROM generate_series('2000-01-01'::timestamp, now(), '1minute') AS x(ts)
;

SELECT count(*) FROM van;
-- 10095150
SELECT count(*) FROM van GROUP BY (server, vanview);
-- (25 rows)
Jednotlive stlpce vo viacstlpcovych indexoch je potrebne radit v poradi selektivity a znovupouzitelnosti. Ak query filtruje len podla niektorych stlpcov indexu zlava, vie ho pouzit. A preto sa pouzije index ix_van_server_vanview_rodatum na SELECT min(server), ale uz nie na SELECT min(vanview).
 -- used by min(server), max(rodatum) per server&vanview
CREATE INDEX ix_van_server_vanview_rodatum ON van (server, vanview, rodatum DESC NULLS LAST);
 -- used by min(vanview)
CREATE INDEX ix_van_vanview ON van (vanview);
Test tvojej query pre porovnanie casov:
EXPLAIN (BUFFERS, ANALYZE) select max(rodatum), server, vanview  FROM van GROUP BY server, vanview;
-- actual time=1791.880..1791.936
-- Parallel Seq Scan on van
Pouzil sa Seq scan, napriek tomu, ze existuje ix_van_server_vanview_rodatum, skusim ho zakazat:
SET enable_seqscan = OFF;
EXPLAIN (BUFFERS, ANALYZE) select max(rodatum), server, vanview  FROM van GROUP BY server, vanview;
-- actual time=218.738..3580.570
-- Parallel Index Only Scan using ix_van_server_vanview_rodatum
...este pomalsie - zda sa, ze planner funguje spravne

Kedze mame pomerne male mnozstvo kombinacii ((count(server)*count(vanview))==25), napadlo ma pouzit index ix_van_server_vanview_rodatum tak, ze mu podsuniem 25 roznych hodnot, co by bezalo so zlozitostou O(25 log 10^7). Takze potrebujem ziskat 25 unikatnych hodnot. Lenze SELECT DISTINCT je este pomalsi, nez SELECT server, vanview GROUP BY server, vanview:
SELECT DISTINCT server, vanview FROM van
-- Time: 1878,862 ms (00:01,879)
SELECT server, vanview FROM van GROUP BY server,vanview
-- Time: 980,318 ms
Korelovana subquery je potom obmedzena pomalostou DISTINCT/GROUP-BY:
SELECT max(rodatum), server, vanview
   FROM van
   WHERE (server,vanview) IN (SELECT DISTINCT server, vanview FROM van)
   GROUP BY server,vanview
;
-- Time: 5028,114 ms (00:05,028)

SELECT (
   SELECT max(v.rodatum)
      FROM van AS v
      WHERE (v.server, v.vanview) = (vv.server, vv.vanview)
   ), server, vanview
   FROM (SELECT server, vanview FROM van GROUP BY server,vanview) AS vv
   GROUP BY server, vanview
;
--Time: 984,181 ms
...je vidiet mierne zrychlenie, ale stale sme v radoch sekund.

A tu prichadza trik s rekurzivnou CTE pre indexovany DISTINCT v kombinacii s horeuvedenou korelovanou subquery:
--EXPLAIN (BUFFERS, ANALYZE) 
WITH RECURSIVE t AS (
   SELECT min(server) AS s FROM van
   UNION ALL
   SELECT (SELECT min(server) FROM van WHERE server > t.s)
   FROM t WHERE t.s IS NOT NULL
)
, tt AS (
   SELECT min(vanview) AS v FROM van
   UNION ALL
   SELECT (SELECT min(vanview) FROM van WHERE vanview > tt.v)
   FROM tt WHERE tt.v IS NOT NULL
)
SELECT (
   SELECT max(rodatum)
      FROM van
      WHERE server = s
        AND vanview = v
   ), s, v
   FROM t, tt
   WHERE s IS NOT NULL
     AND v IS NOT NULL
;
-- Time: 1,679 ms
Pre vysvetlenie vid https://wiki.postgresql.org/wiki/Loose_indexscan
12.3.2019 14:24 marek
Rozbalit Rozbalit vše Re: postgres optimalizace dotazu

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...

12.3.2019 14:53 OldFrog {Ondra Nemecek} | skóre: 32 | blog: Žabákův notes | Praha
Rozbalit Rozbalit vše Re: postgres optimalizace dotazu
Jakou máte verzi Postgres?
-- OldFrog
12.3.2019 15:18 marek
Rozbalit Rozbalit vše Re: postgres optimalizace dotazu

postgres (PostgreSQL) 9.2.24

CentOS Linux release 7.6.1810 (Core)

marek

12.3.2019 15:30 OldFrog {Ondra Nemecek} | skóre: 32 | blog: Žabákův notes | Praha
Rozbalit Rozbalit vše Re: postgres optimalizace dotazu
Hmm, na 10 to funguje stejně pomalu a 11 ještě není v repositáři. Takže to spíš vypadá že novější verze by to neřešila.
-- OldFrog
12.3.2019 15:55 EtDirloth | skóre: 10
Rozbalit Rozbalit vše Re: postgres optimalizace dotazu
Jasne, nevsimol som si tie foreign keys. Takze potom nepotrebujeme tu rekurzivnu query a bude stacit korelovana subquery - co je ekvivalentne tomu foreach:
EXPLAIN (BUFFERS, ANALYZE)
SELECT (
   SELECT max(rodatum)
      FROM van
      WHERE server  = server.id
        AND vanview = vanview.id
   ), server.id, vanview.id
   FROM server, vanview
   WHERE label LIKE 'nom%'
;
-- actual time=0.058..0.321
-- Index Only Scan using px_server_id_label_nom
-- Index Only Scan using ix_van_server_vanview_rodatum
...ten cas + index px_server_id_label_nom som nameral s pouzitim kodu nizsie.

Asi v tabulke servers nebude vela zaznamov, ale ak nahodou ano, ta WHERE (label LIKE 'nom%') sa da podporit parcialnym indexom px_server_id_label_nom:
CREATE TABLE server (
   id    int  NOT NULL
 , label text NOT NULL
);
CREATE INDEX px_server_id_label_nom ON server (id) WHERE (label LIKE 'nom%');

WITH RECURSIVE t AS (
   SELECT min(server) AS s FROM van
   UNION ALL
   SELECT (SELECT min(server) FROM van WHERE server > t.s)
   FROM t WHERE t.s IS NOT NULL
)
INSERT INTO server
   SELECT s, 'nomnom' AS label FROM t WHERE s IS NOT NULL
;
INSERT INTO server
   SELECT s, 'omnom' AS label FROM generate_series(1,11111,1) AS x(s)
;
Tu je vidno, ze sa pouzije parcialny index px_server_id_label_nom:
EXPLAIN (BUFFERS, ANALYZE)
SELECT id FROM server WHERE label LIKE 'nom%';
-- actual time=0.019..0.020
-- Index Only Scan using px_server_id_label_nom
EXPLAIN (BUFFERS, ANALYZE)
SELECT id FROM server WHERE label LIKE 'nomn%';
-- actual time=0.008..1.060
-- Seq Scan on server
...rozdiel oproti seq scanu nad takto malou tabulkou je sice v niekolkych radoch, ale aj ta milisekunda pre seq scan je nepatrna, takze sa to oplati hlavne pre vacsie tabulky (co do poctu riadkov aj stlpcov).

Pre uplnost prikladam aj moj CREATE TABLE mock tabulky vanview:
CREATE TABLE vanview (
   id    int  NOT NULL
);
INSERT INTO vanview
   SELECT v AS label FROM generate_series(5,9,1) AS x(v)
;
12.3.2019 16:21 marek
Rozbalit Rozbalit vše Re: postgres optimalizace dotazu

Jeste jednou dekuji.

Sice je u mne stale rychlejsi ta funkce, ale mnohe jsem si z Vasich prispevku odnesl.

marek

Založit nové vláknoNahoru

Tiskni Sdílej: Linkuj Jaggni to Vybrali.sme.sk Google Del.icio.us Facebook

ISSN 1214-1267   www.czech-server.cz
© 1999-2015 Nitemedia s. r. o. Všechna práva vyhrazena.