Dotaz: Jak v postgresu na indexované vyhledávání podle n-tého znaku ve sloupci

[u-rob-o-ros]

Ahoj. Potřebuju vyhledávat v obrovské tabulce záznamy, které obsahují třeba na 3. pozici ve sloupci písmeno x, tj.

sl1 sl2
1   --xDas
2   a-xas
3   --x--

Udělat to pomocí LIKE trvá strašně dlouho, proto bych rád věděl, zda to jde udělat indexovaně? Je mi víceméně jedno jestli se bude hledat třeba podle sloupce typu VARCHAR či např. podle INTu, kde by se hledalo binární operací AND, ale potřebuju na to vytvořit a používat indexy. Jak na to?

Odpovědi

[okbob]

v 9.1 lze pouzit trigramy a indexovat i like, kde je zolik vlevo.

Jinak asi by bylo mozne navrhnout i neco efektivnejsiho, ale vyzadovalo by to vlastni typ indexu - a vicemene psani v C. Pak by to asi mohlo byt opravdu efektivni

Pripadne si pro kazdou pozici muzete udelat index - ale nejde to tak jak si predstavujete.

CREATE TABLE foo(s text);
CREATE INDEX ON foo USING GIN ((substring(s FROM 1 FOR 1));
CREATE INDEX ON foo USING GIN ((substring(s FROM 2 FOR 1));
CREATE INDEX ON foo USING GIN ((substring(s FROM 3 FOR 1));

SELECT * FROM foo WHERE substring(s FROM 3 FOR 1) = 'x';

tahle podminka bude mit dost mizernou selectivitu - dost mozna ze problem je jenom v like a stacilo by nepouzivat like

SELECT * FROM foo WHERE substring(s FROM 3 FOR 1) = 'x';

Jeste by slo pouzit hstore:

postgres=# create or replace function pack(text)
returns hstore as $$
SELECT string_agg(i || '=>' || substring($1 FROM i FOR 1),',')::hstore 
FROM generate_series(1,length($1)) g(i); $$ 
LANGUAGE sql IMMUTABLE;
CREATE FUNCTION
postgres=# select pack('Pavel');
                       pack                       
--------------------------------------------------
 "1"=>"P", "2"=>"a", "3"=>"v", "4"=>"e", "5"=>"l"
(1 row)

postgres=# select * from xx;
   a   
-------
 Pavel
(1 row)

postgres=# CREATE INDEX ON xx USING GIN((pack(a)));
CREATE INDEX

postgres=# SELECT * FROM xx WHERE pack(a) @> '3=>v';
   a   
-------
 Pavel
(1 row)

postgres=# set enable_seqscan to off;
SET
postgres=# explain SELECT * FROM xx WHERE pack(a) @> '3=>v';
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Bitmap Heap Scan on xx  (cost=12.25..16.51 rows=1 width=32)
   Recheck Cond: (pack(a) @> '"3"=>"v"'::hstore)
   ->  Bitmap Index Scan on xx_pack_idx  (cost=0.00..12.25 rows=1 width=0)
         Index Cond: (pack(a) @> '"3"=>"v"'::hstore)
(4 rows)

postgres=# SELECT * FROM xx WHERE pack(a) @> '3=>v, 4=>e';
   a   
-------
 Pavel
(1 row)

postgres=# SELECT * FROM xx WHERE pack(a) @> '3=>v, 4=>x';
 a 
---
(0 rows)

Ale co bude rychlejsi netusim - hstore je hodne genericky, takze pokud by tam byla nizka selectivita, tak by to nemuselo byt efektivni

[Filip Jirsák]

To vypadá, že v tom textu jsou zakódovaná nějaká data. Databázově nejčistší by bylo ta data rozkódovat a uložit samostatně – třeba i pravidlem při insertu a updatu. Pak už si můžete udělat indexy na ta rozdělená data.

[camel1cz]

Souhlas... a nebo jít cestou oindexování volání funkce - viz CREATE INDEX, je tam příklad na vytvoření indexu nad lower()

[u-rob-o-ros]

Víceméně se snažím vyhnout použití 2 tabulek a časově náročnému spojování po každém dotazu nebo částečné redundanci. Abych upřesnil co to má dělat: mám N měřících stanic, jejichž počet se občas mění. Každá stanice ukládá do tabulky každou vteřinu měření. Měření jsou často naprosto identická a proto se snažím uspořit data tím, že místo 10 skoro identických řádků uložím jeden, který bude mít speciální rozlišovací sloupec určující jednotlivé stanice. Ten sloupec by byl třeba znakový "xxx-xxx-" (data jsou stejná pro 1. 2. 3. 5. 6. a 7. stanici) nebo celočíselný používající k rozlišení boolovskou algrebru (1 or 2 or 4 or 16 or 32 or 64). Tímto ukládáním se totiž rapidně sníží množství ukládaných dat.

[Filip Jirsák]

K tomu by se lépe hodil bitový řetězec, ale nevím, jak jej PostgreSQL umí indexovat. Spíš bych ale použil normální spojení dvou tabulek. Nevím, co je to za mýtus o časově náročném spojování – to spojení žádnou zátěž nepřidá, podstatné je to hledání podle indexu. A tomu se stejně nevyhnete, naopak při použití 2 tabulek můžete použít standardní indexy a operace, na které je ta databáze už desítky let optimalizována.

[kuka]

Tak spojeni velkych tabulek muze byt vypocetne velice narocne, to neni zadny mytus, zalezi na konkretni situaci. K optimalizacim je treba pristupovat vzdy s ohledem na ocekavane scenare. Jestlize napriklad tech stanic je 10 a ocekava se, ze dotaz tedy vybere zhruba desetinu zaznamu z tabulky, tak je prinos pristupu pres index sam o sobe diskutabilni a vetsina databazi rychleji provede full scan. Like bude typicky mnohem pomalejsi nez substr nebo bitova operace nad ciselnym priznakem, coz muze byt u velkych objemu take hodne videt. Neznam samozrejme konkretni scenar, ale obecne databaze byvaji dobre v praci s velkym poctem radku (a maji na to prostredky - partitiong, komprese...) a slabsi v nejake vnejsi logice (jako zde ten priznakovy retezec). Pomerne caste je ukladani aktualnich dat v neoptimalizovane "pruzne" strukture (napr. okamzite lze pridat dalsi stanici) a konsolidace starsich dat vice nebo mene "natvrdo" podle ruznych kriterii (uspora mista, rychlost konkretnich dotazu, moznost drilovani v EUL atd.)

[Filip Jirsák]

Tak spojeni velkych tabulek muze byt vypocetne velice narocne, to neni zadny mytus, zalezi na konkretni situaci.

Je to mýtus. Spojení velkých tabulek nic nestojí. Nákladné je vyhledávání. Zkuste si to. Udělejte si SELECT na spojení tabulek, a pak soustavu SELECTů, která vybere ta samá data bez spojení v databázi (spojení uděláte v aplikaci). To druhé bude pomalejší – hledání použije stejné indexy, ale v druhém případě bude víc režie se spojením, parsováním dotazů apod.

[kuka]

Databaze zvoli pro vyhodnoceni dotazu nejaky plan a podle nej potom postupuje - nacita data do pameti, provadi joiny, sorty, agregaci... Kazda z techto operaci ma svou cenu z hlediska spotreby pameti a vypocetniho casu. Jestlize je zvolenou operaci napr. hash join tabulek o radove milionech zaznamu, trva takova operace i na hodne nabusenem zeleze nekolik desitek vterin. Pokud mam data rovnou v jedne tabulce, je cena jejich "spojeni" nulova, je to treba jeden ze scenaru pouziti materializovanych views. O jakesi "aplikacni nahrade" nebylo reci, v aplikaci samozrejme nelze dosahnout vykonu, jaky ma pro tento typ operaci databaze, ktera je na to roky optimalizovana a ma k datum pristup na nizke urovni.

[Filip Jirsák]

Spojení se dělá vždy hledáním nějaké hodnoty. Přičemž když si to rozdělíte a podíváte se na ty náklady zvlášť, ono spojení má nulový náklad a hledání má nenulový náklad. Takže jestli přidáte to spojení nebo ho odeberete, nemá to na výkon vliv. Následující dva dotazy jsou pro databázi stejně náročné, přestože první používá spojení tabulek a druhý ne.

SELECT A.*
FROM
  table1 A
  JOIN table2 B ON (A.id = B.a_id)
WHERE
  A.id = 1

SELECT A.*
FROM
  table1 A
WHERE
  A.id = 1

[Šangala]

K čemu tam to B je, být DBE, tak na celý JOIN kašlu, a rozdíl nákladů bude to kroucení hlavou nad B.

[Filip Jirsák]

No právě.

[Šangala]

Hledáte tedy prvotní příčinu zvýšení náročnosti?
Tedy něco jako, že spojení tabulek nic nestojí, protože to co něco stojí, jsou ty operace nutné proto spojení?

[Filip Jirsák]

Ta operace nutná pro spojení je vyhledávání. A když tu operaci vyhledávání vyvolám jinak, než při spojování tabulek, bude stát úplně stejně.

[kuka]

Ne ze by na tom z hlediska teto diskuse zalezelo (ucel toho prikladu nechapu), ale ty dotazy nejsou ekvivalentni a na tabulku B se vykaslat nelze.

[Šangala]

Hm, jo, to mi ujelo (ale třeba někdy jo )…

[kuka]

Striktne vzato nejsou stejne narocne, protoze v prvnim pripade se cte tabulka B (nebo jeji index), coz nema nulovou cenu. Nicmene bavit se o takovych prikladech nema v zasade smysl - samozrejme ze join jednoho zaznamu za pouziti indexu nema vysoke naroky na vykon. Neco jineho ovsem uz ovsem muze byt join vsech zaznamu mezi velkymi tabulkami. Nejde o to, jak se napise dotaz, ale jak se navrhne datovy model. Jestlize se napriklad rozhodnu pro nejakou formu redundance, mohu si joiny proste zcela usetrit. Pri analytickych dotazech se casto pracuje s velkym objemem dat, nezajima me napriklad jeden konkretni klient, ale top x klientu s nejvetsim objemem obchodu. V principu musim v takovem pripade projit vsechny zaznamy o obchodech a pokud je mam v nekolika tabulkach, tak tyto musim spojit a vysledek pak mohu agregovat. Pokud mam data v jedne tabulce, tak nic nespojuju a nektere dotazy jsou i radove rychlejsi. Samozrejme ne vsechny, pokud se ptam na jednoho konkretniho klienta, tak zadne meritelne zrychleni nepocitim a pritom mam starosti s udrzenim konzistence vzhledem k redundanci, data maji o neco vetsi fyzicky objem atd. Typicky se takto konsoliduji data, u kterych uz nedochazi ke zmenam a datovy model se dela na miru ocekavanym typum dotazu.

[Filip Jirsák]

Já už to asi jinak napsat nedokážu. Ta drahá operace je vyhledávání. V joinu je vždy skryté vyhledávání. Ale to spojení nepřidává žádné náklady navíc, nad to vyhledávání. Takže myslet si, že když místo spojení použiju vyhledávání, něco tím ušetřím, je to nesmysl. Můžu ušetřit tím, že jinak postavím dotaz, udělám jiné indexy, jinak rozvrhnu data. Ale pokud dva dotazy povedou na použití stejných indexů ve stejném pořadí a se stejnými daty, a v jednom případě půjde o spojení a v druhém o čisté hledání, nebude v tom z hlediska náročnosti operace žádný rozdíl, to spojení nepřidá žádnou složitost navíc k tomu vyhledávání.

[kuka]

Ja vubec nechapu, co je podle tebe spojeni a co vyhledavani a proc na tom zalezi. Dotazem snad vzdycky neco hledam, proc bych ho jinak delal? Zda je k takovemu hledani treba join konkretnich tabulek zalezi na datovem modelu. Co rikam je, ze kdyz dam data do jedne tabulky, tak nemusim delat join. Kdyz je mam ve dvou, tak musim delat join (lepe receno databze ho musi udelat). Napriklad pokud mam v tabulce klient priznak, zda ma kreditku, a chci zjistit sumu operaci klientu bez kreditky, tak musim klienty spojit s operacemi, jinak nepoznam, zda mam danou operaci zapocitat. Pokud je klientu bez kreditky milion a operaci sto milionu, tak takove spojeni pobezi docela dlouho. Pokud budu mit priznak o kreditce rovnou v operacich, tak zadne spojeni neprovadim a rovnou agreguju v ramci fullscanu tabulky operaci (ktery musi probehnout v obou scenarich).

[Filip Jirsák]

Jenže tady řešíme jiný případ. Buď budu mít data ve dvou tabulkách a indexy, přes které je dokážu propojit. Nebo budu mít data v jedné tabulce a nad ní takový index/indexy, které budou to propojení dvou tabulek simulovat. Já tvrdím, že to spojení dvou tabulek nepřináší žádnou zátěž navíc a principiálně bude obojí stejně rychlé, protože se bude používat stejný typ indexů nad stejnými daty.

[kuka]

V mnou uvadenem prikladu se v zadne rozumne databazi indexy nebudou pri spojeni pouzivat, to by trvalo pul dne. Kdyz mam data v jedne tabulce, tak proc bych simuloval nejake spojeni, to je blabol. Evidentne neznas zakladni algoritmy pouzivane v databazovych strojich. Na tom neni nic spatneho a pro radu aplikaci transakcniho typu, jako jsou ruzne eshopy apod., to nemusi vubec vadit, take jsem to prilis neznal, dokud jsem to nezacal potrebovat. Ale nesir prosim bludy typu ze spojeni velkych tabulek nic nestoji.

[Filip Jirsák]

Proč se k tomu pořád vyjadřujete, když jste nepochopil, o čem se bavíme? Tak se prostě smiřte s tím, že to píšu nesrozumitelně a vám není dáno to z mého textu pochopit. Já jsem nikdy nenapsal, že spojení tabulek nic nestojí, takže celé vaše komentáře na tom postavené jsou k ničemu.

[okbob]

Pro časové řady jsou v PostgreSQL pole - pro každou stanici a hodinu můžete mít pole o 3600 prvcích a nemusíte provádět ručně takovéhle ošklivosti - když už používáte relační db, tak není ani praktické snažit se do db ukládat závislé magické konstanty - to už pak je lepší cpát data do souboru jelikož na takhle deformovaných datech se stejně dost špatně píší dotazy.

Pole v PostgreSQL jsou docela vychytávka - navíc pro Vás transparentně se komprimuje obsah. A pokud nebudete extrémně často měnit konfiguraci, tak tím snížíte počet záznamů v tabulkách, cca o 3600/počet stanic - jestli jsem to pochopil, což při režii, kterou má pg, ke každému záznamu může být významná úspora.