Dotaz: Jak dát indexy nebo jak navrhnout, aby to šlapalo

[MP]

Mám následující tabulku, kde jsou burzovní data:

create table admiralmarkets.empty_table_1m( symbol varchar(10) not null, datetime timestamp without time zone not null , open double precision, high double precision, low double precision, close double precision, tick_volume integer, spread integer, real_volume integer );

Unikátní je kombinace symbol + datetime ("datetime" mám jako název pole, snad to nevadí...?)

Jak to postavit aby se dobře dělaly selekty typu select * from admiralmarkets.empty_table_1m where datetime between .... and symbo in ('AA', 'BB' 'CC');

---

---

Odpovědi

[EtDirloth]

• Spravil by som dvojstlpcovy UNIQUE INDEX bud na (symbol, "datetime") alebo ("datetime", symbol) - poradie by som volil podla velkosti time spanu v selecte a tiez variability symbolov v tabulke. EXPLAIN konkretnej query na konkretnych datach napovie.
• Vykonanie dotazu by mozno zrychlilo vyclenenie symbolov do inej tabulky, napr. (smallint primary key, varchar(10) not null) a previazanie cez cudzi kluc.
• Neviem o aku DB ide, ale ak je to PostgreSQL, tak namiesto double precision by som pouzil numeric.

[OldFrog {Ondra Nemecek}]

Vykonanie dotazu by mozno zrychlilo vyclenenie symbolov do inej tabulky, napr. (smallint primary key, varchar(10) not null) a previazanie cez cudzi kluc

Jak to?

[Filip Jirsák]

Protože SMALLINT se indexuje lépe, než VARCHAR(10).

[EtDirloth]

priklady pre pgsql (stale nepozname cielovu DB):

smallint je fixed-size a ma len 2 bajty (a dovoluje pass-by-value), teda je efektivnejsi pri spracovani a indexovani, jednoducho nema overhead, ktorym "trpi" varlena;

smallint je mensi, takze sa zmensi velkost riadku a teda sa ich viac zmesti na stranku, takze sa zmensuje IO;

[OldFrog {Ondra Nemecek}]

Protože SMALLINT se indexuje lépe, než VARCHAR(10).

---

priklady pre pgsql (stale nepozname cielovu DB):

smallint je fixed-size a ma len 2 bajty (a dovoluje pass-by-value), teda je efektivnejsi pri spracovani a indexovani, jednoducho nema overhead, ktorym "trpi" varlena;

smallint je mensi, takze sa zmensi velkost riadku a teda sa ich viac zmesti na stranku, takze sa zmensuje IO;

Ano, asi se lépe indexuje, ale vyhledávání je pak už stejně rychlé, ne?

[Josef Kufner]

Stejně jako se lépe indexuje, tak se i lépe porovnává, když se prochází index. I když tam už to nebude tolik znát, jako při sestavování indexu.

[OldFrog {Ondra Nemecek}]

Teoreticky ano, ale pochybuju že by to byl významný rozdíl.

[Josef Kufner]

Na pár tisících řádcích to možná bude měřitelné. U milionů to znát už bude.

[Xerces]

Můžu se zeptat proč tam je primary key ta kombinace času a symbolu? To jako že nemůže existovat více záznamů se shodným symbolem v jeden čas? Opravdu? Není to zbytečné omezení? Já bych zvážil vytvoření umělého klíče, místo multiklíče nad existujícími sloupci a jednotlivé sloupce indexoval zvlášť. Chybí informace o použité db, to je dost podstatné pro případné návrhy optimalizací.

[MP]

Ano, nemůže, teda lépe řečeno nesmí nastat situace, aby pro jeden symbol existoval více jak jeden záznam se stejným časem. Kombinace čas+symbol musí být unikátní.

[dustin]

Jak je to "nesmí" ošetřeno? Samozřejmě pokud bude primární klíč obojí, pak si to hlídá sama db (ale aplikace musí počítat s chybou query (což by stejně měla, ale...)

[Michal Kubeček]

Většinou není čas jako součást primárního klíče nepříliš vhodný (mockrát už jsem viděl timestamp vytvoření záznamu nesmyslně použitý jako primární klíč místo generovaného číselného identifikátoru), ale existují situace, kdy to smysl dává, protože je timestamp přirozeným identifikátorem. Např. pokud budu zpracovávat hodnoty z meteorologických stanic snímané třeba každou hodinu, je přirozené jako primární klíč použít kombinaci id stanice, id měřené veličiny a čas měření.

[dustin]

Jasně, pokud mám opravdu zajištěnou jedinečnost kombinace, pak nic proti timestampu v primárním klíči. Ale taky by to zde mohlo být jen "malá šance, že se potkají", proto se ptám.

[Filip Jirsák]

V takovém případě by ale měla být u toho datového typu uvedena přesnost. Protože je potřeba přesnost nastavit stejně, jakou mají ta vstupní data (když mi budou meteorologické stanice poskytovat data s přesností na desetinu sekundy, bude chyba, pokud to budu v databázi ukládat s přesností na sekundy nebo i s přesností na milisekundy – akorát v tom druhém případě ty chyby budou nejspíš záhadnější). Spoléhat na to, že výchozí hodnota přesnosti v databázi je zrovna stejná, jako u vstupních dat, je cesta do pekel.

[EtDirloth]

Mam problem pochopit, co sa riesi v tomto vlakne. Z povodnej otazky sa mi data javia ako time-series. Na granularite ani presnosti takemu klucu nezalezi. Ak dojde ku kolizii hodnot, potom prvy vyhrava. Zatial to nevidim ako "cestu do pekel", ale mozno na peknom pekelnom priklade by to bolo zjavnejsie.

Stlpec "datetime" by sice islo abstrahovat do formy dalsej tabulky so zoznamom timestampov a cudzieho kluca, ale pokial by som nebol tlaceny potrebou optimalizacie (vid #5) na hranice moznosti, tak by som to povazoval za zbytocny over-engineering.

[Filip Jirsák]

MP tvrdí, že kombinace čas+symbol musí být unikátní. Představte si následující data:

Čas                       Symbol 
2016-05-24 17:15:59.030   A
2016-05-24 17:15:59.035   A
2016-05-24 17:15:59.123   A

To mohou být tři unikátní záznamy, mohou to být tři „duplicitní“ záznamy, nebo dva duplicitní záznamy a jeden unikátní. Záleží na tom, s jakou přesností čas rozlišujete.

Když MP tvrdí, že ta kombinace musí být unikátní, chápu to tak, že to tak je z povahy okolního světa, že ta data prostě takto unikátně vznikají. Pak ale potřebuje vědět, jakou přesnost mají ta reálná data, a stejnou přesnost pak nastavit i v databázi.

Také to ale může znamenat, že na vstupu mohou být duplicitní záznamy, a splnění podmínky „musí být unikátní“ se musí zařídit až při vkládání dat do databáze (třeba způsobem, že první nebo poslední vyhrává).

Zatial to nevidim ako "cestu do pekel", ale mozno na peknom pekelnom priklade by to bolo zjavnejsie.

Když budete mít na vstupu údaje s přesností na desetinu sekundy, a sloupec bude s přesností na sekundy, můžete na vstupu získat duplicitní údaje. Do databáze je nezapíšete a o data přijdete.

Když budete mít na vstupu data s přesností na sekundy a ukládat je budete s přesností na milisekundy, a budete potřebovat třeba 5 minut starý záznam, vezmete aktuální čas, odečtete od něj 5 minut a výsledný údaj budete hledat v databázi. A nenajdete nic, protože hledaná čas bude mít na místě milisekund nejspíš nějaké nenulové číslo, jenže v databázi budou samé nuly.

[Michal Kubeček]

Když budete mít na vstupu data s přesností na sekundy a ukládat je budete s přesností na milisekundy, a budete potřebovat třeba 5 minut starý záznam, vezmete aktuální čas, odečtete od něj 5 minut a výsledný údaj budete hledat v databázi. A nenajdete nic, protože hledaná čas bude mít na místě milisekund nejspíš nějaké nenulové číslo, jenže v databázi budou samé nuly.

Nebo s tím budu počítat a vezmu poslední záznam před daným časem, poslední záznam po něm nebo interpolaci. Nebo ta tabulka může být jen mezistupněm pro generování kumulovaných dat. Nejde naslepo tvrdit, že něco je špatně, pokud nevíte, k čemu ta data slouží a jak se s nimi pracuje.

[Filip Jirsák]

Pokud s tím budu počítat, dělám to vědomě a není to případ „spoléhám na to, že výchozí hodnota přesnosti je zrovna ta, kterou potřebuji“, o kterém jsme tvrdil, že je špatně.

Když někde vidím timestamp without time zone not null a u toho komentář, že to musí být unikátní, rovnou se ptám, při jaké přesnosti to má být unikátní. Je možné, že to autor ví, ale pak je vhodné to doplnit i do té definice, čímž se těm otázkám předejde.

[MP]

Ano, je to postgress. Díky za odpovědi. Index na datetime a symbol jsem tam dal. Ještě by mě zajímalo jak zrychlit načítání. Použil jsem pomocnou tabulku bez indexů kam to načtu a pak to přesypu do cílové tabulky se stejnou strukturou, ale i tak je to pomalé.

[Filip Jirsák]

Čemu říkáte „načítání“? Co znamená „pomalé“? Popište, o co se snažíte,a co se vám na tom nezdá.

[Tonda]

Ahoj, U nas se to dela trosku jinak nez to co navrhujete. Strasne zalezi na tom co mate za data a hlavne kolik toho je

Nas system uklada za nejakych 16 hodin neco kolem 250mil. radek do database a to je docela dost takze jsem museli hodne optimilizovat

1) myslim ze index na datetime and symbol je nanic, pokud pouzivate insert vzdy se vam po transakci bude prepocitavat index to bude asi duvod proc rikate ze to mate pomale, zkuste copy from stdin 2) pouzivejte partitions my mame partitions per day. takze pokud potrebuju pracovat s jednim dnem pracuju s mene daty. udelejte si views abyste pracoval s co nejmene daty 3) mysql, oracle, sybase, db2 ani informix nam nedali dost rychlosi ulozit co potrebujem v realnem case, Je mi trapne priznat ale MSSQL to zvladlo - kdyz jsme to ukazali Microsoftu neverili nam . Ted pouzivame vertica, KDB a zkousime pgsql - zatim dobry ale nemame tam 100% nasich data takze jeste nevime presne 4) napiste is jiny load a zapoment na index pres dva sloupce, ano na DB urovni je to jak to ma byt ale budete tim trpet, loader tohler vyresi, size za cenu toho ze bude muset vedet o vsech symbolech na den co loadujete ale to se da chytre vyrseit. 5) data co popisujete nevypadaji tak strasne, mate tam vlastne jen symbol,den,open and close price a par dalsich blbosti to by se melo veji do nejakych 2 milionu radek za den 6) 100% musite rozumet vasim datum jinak oprimalizaci neudelate

Pokud mate tick data - coz je mozne jen nam to nerikate, zalezi na tom kolik toho je, je trba prizpusobit strukturu database . A asi nejlepe nepouzivat SQL ale neco jako vertica nebo KDB, my do toho cpemene zatim neco kolem 0.7TB/den a ani o tom nevime.

je potreba zkouset

[mongodb]

Ja len zo zaujimavosti sa chcem spytat, ze preco pre ukladanie takychto dat nevolite radsej nosql databazu, ktora je na to stavana? To robite tak komplikovane selecty ze potrebujete sql syntax a pre nosql to napisat neviete, alebo aky je dovod?

[MP]

Díky za odpovědi. No, zkusil jsem partitioning, ale buď jsem ho udělal blbě nebo.. zkrátka to nepomohlo. Holt nejsem DB expert.. tak jsem zvolil nejjednodušší řešení: Valím to do souborů a ančítám z nich zpět.. .. Ty soubory mám na SSD, takže rychlost OK. Možná je taky chyba že ten Postgres běžel pod windows.. no.. Když budu uvažovat o DB, tak si to raděj nechám někým napsat.. A ticková data to nejsou.. jen minutová, s výhledem že budou vteřinová.. Podle mě ticková data moc na SQL databáhi nejsou vhodná..