Egy nagyon nem hatékony SQL query (PostgreSQL)

Adatbázis: SQL, XML DB

Sziasztok.
Úgy tűnik nem vagyunk barátok az SQL-lel. Csináltam egy lekérdezést, a végeredmény ugyan jó, de elképesztően nem hatékony :

WITH pl AS (SELECT * FROM phs_links WHERE phs_link_type2 = 'Front' AND port_id2 = ?)
SELECT pll.*, plr.*, ppr.*
FROM pl AS pll
JOIN interfaces AS il ON pll.port_id1 = il.port_id
JOIN mactab AS m ON il.hwaddress = m.hwaddress
JOIN phs_links AS plr ON m.port_id = plr.port_id1
JOIN pports AS ppr ON plr.port_id2 = ppr.port_id
WHERE min_shared(plr.port_shared, ?::portshare) <> 'NC'::portshare

(Az adatbázis : http://svn.kkfk.bgf.hu/lanview2.doc/database/lanview2.html )
Az első SELECT (a WITH után) egy, vagy két rekordot ad vissza (elvileg max. 4-et), a végeredmény pedig legfeljebb egy rekord.
A min_share() függvényben vannak RAISE INFO parancsok, és ebből az látszik, hogy az SQL szerver egy SELECT-et gyárt az egészből,
és minden rekordra végrehajtja az összes feltételt. Vagyis a min_share() által kezdeményezett kiírás az általam becsült 1-2 helyett,
több százszor-ezerszer jelenik meg.
Lehet ezt a lekérdezést valahogy optimalizálni.
Direkt azért írtam meg a SELECT-et két lépésben, mert szerintem így gyorsabb(-nak kéne lennie) , de úgy tűnik valamit már megint félreértettem.

  • 2671 megtekintés

Kiadtam a

EXPLAIN ANALYZE WITH pl AS (SELECT * FROM phs_links WHERE phs_link_type2 = 'Front' AND port_id2 = 22015)
SELECT pll.*, plr.*, ppr.*, m.*
FROM pl AS pll
JOIN interfaces AS il ON pll.port_id1 = il.port_id
JOIN mactab AS m ON il.hwaddress = m.hwaddress
JOIN phs_links AS plr ON m.port_id = plr.port_id1
JOIN pports AS ppr ON plr.port_id2 = ppr.port_id
WHERE min_shared(plr.port_shared, 'A'::portshare) <> 'NC'::portshare

parancsot. Az eredmény : https://explain.depesz.com/s/749L
Én ettől okosabb nem lettem.

Igy van.

phs_links_table 4 163.435 ms 76.1 %
Seq Scan 2 163.399 ms 100.0 %

És ha megnézzük hol a gond, akkor a másik fülön látjuk:

Seq Scan on phs_links_table (cost=0.00..541.14 rows=1,941 width=93) (actual time=0.167..85.940 rows=1,875 loops=1)
Filter: (min_shared(port_shared, 'A'::portshare) <> 'NC'::portshare)
Rows Removed by Filter: 76

Gyakorlatilag végig kell mennie az összes soron, és végrehajtani ezt a szűrést.Ettől lesz lassú.

Igen ezt nem értem.
Ha nem "optimalizálná" azt amit leírok, vagy ha már optimalizál, akkor előszór a nem költséges feltételeket hajtaná végre, nem pedig minden rekordon minden feltételt, ha kell ha nem.
Azt hittem, hogy átverhetem: írtam egy függvényt az első SELECT helyett, hogy talán akkor hajlandó az a SELECT-et önállóan végrehajtani, de nem, ő okos "kioptimalizálta".
Szóval nekem nincs időm ezzel ennyit szarakodni. Lesz két query, az első a maga 0-4 rekordjával, C++-ban kiszűröm azt az egyet, majd lekérdezem ami még kell. Ez így csak kb. 1000-szer gyorsabb, és nem kell átláthatatlan kifejezéseket kreálnom a min_shared() helyett. Sőt, tulajdonképpen az egész átláthatóbb lesz.

( kikuchiyo | 2018. 10. 30., k – 19:45 )

A Postgresql-ben a CTE optimization fence-t kepez, emiatt lassabb lehet, mint mondjuk egy subselect. A te querydet talan self-joinnal is meg lehetne fogalmazni, de most mar faradt vagyok vegiggondolni.

( dotnetlinux | 2018. 10. 30., k – 21:28 )

Ha a WHERE nem indexelt adatokkal dolgozik, akkor a JOIN-ok által összerakott teljes halmazon (kis optimalizálással csak azokon amik (nem) érintettek a whereben) végigfut és ellenőrzi a feltételt. Az hogy az eredmény csak 2-4 sor, nem befolyáolja, hogy nagy adatmennyiségen kell végigfutnia.

Csakhogy itt két WHERE van, az elsőnek az eredménye az a 0-2 rekord (max 4), és ez nem tartalmazza a min_shared() függvényhívást, amit viszont mégis meghív minden phs_links rekordra.
Ezért bontottam a lekérdezést két részre, de jól láthatóan feleslegesen, mer az SQL szerver "okosabb".
Én nagyon hiányolom az SQL-bőn a C-ben nagyon hasznos || és && operátorokat, mert így rengeteg kifejezést (kifejezés részletet) kell feleslegesen kifejteni (vagy bonyolultabban leírni), mivel annak értéke már irreleváns.
Ja, és az első WHERE-ben szereplő feltételben a port_id2 index, és része a phs_link_type2 -vel együtt egy egyedi kulcsnak.

Az SQL mindig mindkét oldalát végrehajtja egy AND vagy OR kifejezésnek. A C-ben a || operator abban különbözik az OR-tól, hogy csak akkor hajtja végre a jobb oldalt, ha a bal oldal eredménye hamis, tehát, ha a jobb oldal értéke a végeredmény szempontjából irreleváns, akkor nem fejti ki, és nem pazarol rá processzor időt. Hasonlóan működik a && az AND-al szemben, csak akkor hajtja végre a jobboldalt, ha a bal igaz volt.
Jelen esetben, ha nem értékelné ki az összes feltételt, csak az olcsóbbakat, a drágábbakat pedig nem, ha annak értéke már irreleváns, akkor néhány mSec lenne a query, a több perc helyett, nekem meg nem kéne azon agyalni, hogy akkor most hogyan, és ez a fórum téma is teljesen felesleges lenne.

"Az SQL mindig mindkét oldalát végrehajtja egy AND vagy OR kifejezésnek."

Ezt erősen kétlem. Miért értékelné ki mindkettőt, ha nem szükséges? Pont ezért adható meg a tárolt eljárásoknál is, hogy okozhat-e sideeffectet a futtatása. (Ez egyébként egy olyan feature, ami meg pont a programnyelvekből hiányzik nagyon).

https://www.postgresql.org/docs/10/static/xfunc-volatility.html

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Ezt kifejtenéd a fenti lekérdezés tükrében.
Csak azért kérdezem, mert ebben az inkriminált lekérdezésben önkényesen összevon három feltételt, és ezek mindegyikét végrehajtja minden rekordra. Szóval, ha olyan okos, hogy a felesleges dolgokat nem hajtja végre, akkor miért hajtja végre. Az első két feltétel, ami direkt!!! külön!!! SELECT-ben van, kulcsokra hivatkozik, és azokra, amikre ezek nem teljesülnek, teljesen felesleges!!! lefuttatni a min_shared()-et.
Nem vagyok egy nagy SQL guru, de most javítottam a min_shared()-et, IMMUTABLE lett, de ennek semmi hatása. A doksi ugyan azt írja, hogy IMMUTABLE esetén ugyanazon paraméter esetén ugyanaz az eredmény, mindig, és ezt optimalizáláskor kihasználja, ennek ellenére több százszor hívja a függvényt pedig a paraméter értékpárokra van vagy 9 variáció.

Nem a CTE a gond, hanem, hogy batár lassú a min_shared(), ami kétszer meghívodik a phs_links_table -n. Ez teszi ki a 82%-át. Utána meg 14% az UNION miatt egy unique szűrés. Minden más join és hasonló alig 4%-a a querynek.

Ez elég egyértelműen látszik az explainből.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Szerintem az a gond, hogy te imperatív nyelvként gondolsz az SQL-re, holott ez deklaratív. Aztán ezt a deklaravitást vágod tarkón egy imperatív függvénnyel, amit meg kell etetni minden egyes rekorddal és esélye sincs optimalizálnia.

Ez nem egy C fordíto, nem fogja neked inlineosítani a függvényed és átfordítani deklaratívvá a függvényed. Persze, jó lenne, de nem tudja. Ergo: ugyanúgy fel kell neki gyűjteni az egész adattáblát.

Ráadásul most nézem, hogy a függvényed még csak nem is nevezhető IMMUTABLE-nak. Mindjárt az elején ott a PERFORM error, ami egy táblába ír. Ráadásul a RAISE NOTICE-ket se értem, hogy minek. Ahogy nézem kb. 2x2000 rekordod van, amiből kifilterez kb. 75-öt. Írd át egy CASE WHEN-re vagy csináld máshonnan a szűrést. Ez a min_shared() ebben a formában szar, ha két percig fut.

---

Egyébként ez a fenti példa pontosan jól mutatja, hogy mi a baj a C || operátorával: Ha az első feltétel nem teljesül, akkor a második függvény nem hívodik meg. Ergo, teljesen kiszámíthatatlan, hogy fut-e vagy sem. És mivel nem tudod, hogy van-e side-effectje a függvényednek (itt ugye van!), teljesen kiszámíthatatlan, hogy mit csinál a kódod. Éppen ezért egyes helyeken ilyet nem is engednek: 


if (Foo(...) || Bar(...)) {...}

Hanem csak így: 


var a = Foo(...);
var b = Bar(...);

if (a || b) {...}

----

Szerk.: de egyébként vannak további dolgok, amiket nem értek a táblaszerkezetedben. Pl. a phs_links_table táblába minek egy forward mező, ha csak a viewnek kell hozzáraknia? Azt ott is berakhattál volna egy konstanst egy aliasszal.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Hát igen. Az imperativ nyelvekkel jól el vagyok, az SQL félékkel kevésbé.
Nagyon szívesen fogadom, ha valaki beszáll a projektbe, tényleg lenne igény SQL guru-ra.
Az hogy az adatbázis helyenként hülyeségeket tartalmaz annak is köszönhető, hogy az adatbázis valamikor 8 éve lett megalkotva, és az akkori tudásom még a jelenlegit sem érte el, segítségem pedig ugyan annyi volt mint most (Láttad volna azokat a baromságokat, amiket már kijavítottam, sőt, ha láttad volna az előző verzió MySQL adatbázisát, kiégne a szemed :), de működött ...). Egyedül a fórumok vannak, ahol nagyon ügyesen rámutatnak a hibáimra (tisztában vagyok velük, többé-kevésbé, és nem ezt kérdeztem), volt hogy elküldtek kapálni, hátha az jobban illik a kvalitásomhoz, de a megoldás szempontjából is hasznos infó az elég ritka. Egymás melletti elbeszélés annál gyakoribb.
Na mindegy, a min_shared() behelyettesítése szerintem nem megoldás, még ha ezen egy szem lekérdezés szempontjából annak is tűnik. Lekérdezem két lépében, a két rekordból az egy igazit majd C++-ból szedem ki, nem nagy ügy.

A phs_links_table táblában azért van forward mező, hogy ugyan az legyen a rekord szerkezet mint a phs_links VIEW táblának, ennek az API szempontjából van jelentősége (át kell gondolni, lehet sehol sincs kihasználva ez a tulajdonság), az SQL pedig szerintem túléli ezt a rettenetes plusz terhelést. Majd, ha ez lesz a legnagyobb probléma, kijavítom.

Próbálom értelmezni amit írtál, és egyre jobban idegesít, hogy nem tudom.
Egyrészt milyen side effectje van a min_shared_nek() ? Explicit odaírtam, hogy IMMUTABLE. Az nem azt jelenti, hogy nincs side effect? Ettől függetlenül, ha összevon egy query-be két külön leírtat, azzal nem éppen kiváltja a side effect problémát?
Az meg végképp nem értem, hogy megvédted az SQL-t, miszerint nem hajtja végre a felesleges tagjait az AND vagy OR kifejezéseknek, majd az bizonygatod, hogy de, mert azt így kell. Akkor most mi van?

Kevered a dolgokat. A pl/pgsql egy "külső" nyelv, lehetne helyette akármi: Python, perl, c, azonban távoli service hívása, stb. Jelen esetben olyan, mintha egy C/C++-ban írt programja beágyaznál még egy Python interpretert és azt várnád, hogy egy ciklusnak futtatott python kód optimalizálódjon össze a hívó C kóddal.

Ezért nem tud vele mit kezdeni, ezért kell seq scannal végigmennie a táblán és egyesével megetetnie a min_shared-et. Mert egy pl/pgsql kód van benne, nem pedig egy SQL kifejezés. Ha a helyén egy SQL kifejezés lenne (ami per defin8ció) deklaratívan azt mondja meg, hogy te mit akarsz kapni, nem pedig azt, hogy hogyan állítsa elő az eredményt a PostgreSQL (Vagy bármely másik RDBMS). Ha inkrementálni kell 10 rekordban egy számlálót, akkor írsz rá egy queryt, hogy mit csináljon meg a DB és nem betöltesz 10 objektumot, inkrementálsz majd visszaírod (hello ORM, hogy szakadna szét...) Nem foglalkozol azzal, hogy használ-e indexet (van, hogy nem is érdemes), kiértékeli-e az OR másik részét, stb. Igazából, mivel a select elvben immutable adatok kérdez le, elvben irreleváns, hogy mit értékel ki. Avagy, üdvözöllek a funkcionális nyelvek világában. Mondjuk a te problémád továbbra sem ez.

Az, hogy odaírod, hogy immutable, meg nem teszi alapból immutablevá a zárolt eljárásod. Ha megnézed, a második sorban ott egy PERFORM error(), ami minden további nélkül ír máshova. Ez nagyon is volatile. Meg ezekkel igazából az optimalizálónak adhatsz tippeket. Meg ugyanígy nem értem a halom raise noticet se. Minek? Másik absztrakciós szinten van ezeknek a helye: Adatellenőrzésnek beszúráskor, logolásnak meg szintén szerintem nem itt.

(Egyébként nem az lassú, hogy teleszórja a db_errs táblát, mert ez nekiáll logolni?)

(Ui.: Nagyon fájó ezt a sok rövidített mező meg táblanevet átlátni. Nem a 80-as években vagyunk.)

Ez am valami public/open source project?

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Biztos bennem van a hiba, de szerintem, ha odaírom hogy IMMUTABLE, akkor ennek két elfogadható következménye lehet: Hibát jelez, mert szerinte hülyeség. Tudomásul veszi, legfeljebb figyelmeztet, hogy szerinte hülyeség. A harmadik számomra teljesen elfogadhatatlan következmény: leszarja, mert szerinte hülyeség. Ha jól értem szerinted ez utóbbi történik.
A PERFORM error(...) külső adatbázisba ír, pontosabban úgy csinál mintha külső adatbázisba írna, nem hívódik meg elvileg soha, ill. csak akkor ha valamit elkutyultam (hibakezelés). Sejtem hogy mit akarsz mondani, de egyrészt ha azt mondtam, hogy IMMUTABLE, akkor vegye tudomásul, és ne okoskodjon, másrészt ha nem IMMUTABLE, akkor pedig én pont nem ezt a működést várnám.
Újra ideírom mert mintha mindenki elsiklott volna felette. Két SELECT van, az elsőnek van egy eredménye, ami 0-4 rekord, és nincs benne semmilyen függvényhívás. Az optimalizációnak nevezett okosság meg összevonja a második SELECT-el és ezt akkor sem nevezném optimalizálásnak, ha a min_share() esetleg nem szar.
De fentebb írtam, hogy csináltam egy függvényt ami azt a rekord set-et adja vissza mint az első SELECT, és az eredmény ugyan az: a függvény törzsében lévő SELECT-et és a külső SELECT-et összevonja, és az így kreált lekérdezést hajtja végre. Nem kezeli önálló egységként a függvényemet, ennek a logikáját végképp nem értem.
Ja, és még egy érdekesség: Ha az első SELECT egyetlen rekordot sem ad vissza, akkor a semmivel azonnal visszatér, ekkor miért nem kell végigmenni az összes feltételen, ilyenkor nincs egy SELECT-be összevonva az egész? Akkor most önállóan hajtja végre az első (WITH utáni) SELECT-et vagy nem, ha igen, akkor hogy kerülnek a másodikba olyan rekordok, amiket az elő már kiszűrt ???????

Igen, ez egy nyílt forrású projekt (https://github.com/csikfer/lanview2) már volt róla szó a HUP-on minimális érdeklődéssel. Szívesen fogadok hozzájárulásokat, mert kurva nagy falat. A kritikákat kisebb lelkesedéssel várom, azzal tele van a padlás.

Per definició az nem immutable, ha külső adatbázisba ír. Nem is lehet, mert mi van, ha egy connection error-t dob? De igazából mindegy is. Másrészt hogyan kellene kikényszerítenie egy pl/Java esetén az immutabilitást a PostgreSQL-nek? Vagy egy pl/Perl esetén? Vagy egy 3rd party pl/sh esetén?

Nem itt van a gond, hanem, hogy nem tud mást csinálni, mert semmi olyan infója nincs a plannernek, amivel elkerülhetné, hogy ne seq scant csináljon.

Tudsz adni egy db dumpot tesztadatokkal?

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Nincsenek teszt adataim, az éles adatbázison, vagy annak másolatain szoktam dolgozni/tesztelni. Az éles adatbázis viszont szinte a teljes hálózatunkat tartalmazza, tartok tőle, ha kiadnám nagyon fenékbe billentenének. Szerepel a To Do listámban a teszt adatbázis, sok-sok más tétel után. A konkrét problémán különben is túlléptem, de jó lenne megérteni a történteket, mert vagy lecserélem az SQL-t valami másra (mi másra?), vagy végre megértem a működését/logikáját.
Szóval az érveid nekem nem állnak össze (nem álltak, írás közben tesztelgettem):

"Per definíció az nem immutable" Mi van? Az SQL eldönti, hogy immutable vagy volatile? Akkor miért van a szintaxisban, hogy én mondjam meg? Ha én hibásan adtam meg, akkor dobjon kizárást, vagy vegye tudomásul azt amit odaírtam. AZ HOGY FELÜLBÍRÁLJA, FIGYELMEN KÍVÜL HAGYJA AZ ELFOGADHATATLAN, HIBÁS MŰKÖDÉS! Nem kell kikényszerítenie, higgye el, én írtam azt a kurva függvényt nekem KELL tudni mit csinál, ha nem tudom, az az én bajom, majd kijavítom. Az optimalizálást úgy kell végrehajtani, ahogyan én megadtam a függvény tulajdonságát, nem kell vizsgálgatni, nem kell kikényszeríteni semmit, megkérdezte, higgye el. -- Ez eddig ugye az én véleményem. -- Ezzel szemben a tesztjeim azt sugallják, hogy az SQL nagy ívben leszarja miket írogatok a függvény definícióba, ha szerinte VOLATILE, akkor VOLATILE, nem szól, ha ez engem zavar, az az én bajom.

De ettől függetlenül szerintem én azt írtam le SQL-ben, hogy csináljon egy SELECT-et, amire megadtam két szűrési feltételt, ebben nincs szó egyetlen függvényhívásról sem. Ennek a SELECT-nek az eredménye csak néhány rekord. És ezekre a rekordokra írtam fel egy másik SELECT-et, szűrési feltételt, ami tartalmazza a vitatott függvényt. -- Ez eddig ugye az én véleményem. -- Ezzel szemben az történhet, hogy az értelmező nem hajtja végre a SELECT-eket önállóan, hanem begyűjti a feltételeket egy kupacba, majd ezt hajtja végre. Az összevont kupacban már logikus az amit csinál, főleg mivel az IMMUTABLE tulajdonságot volt szíves leszarni.

Nem állítom, hogy az SQL működése logikátlan (ahhoz túl jól működik), csak annyit hogy a logikája nekem nagyon idegen. Ugyan úgy ahogy az legtöbb adatbázis guru gondolkodása, így legtöbbször (vagy mindig) elbeszélünk egymás mellett.

""Per definíció az nem immutable" Mi van? Az SQL eldönti, hogy immutable vagy volatile? "

Nem eldönti, te hazudsz az RDBMS-nek: azt mondod a tárolt eljárásodról, hogy immutable, közben meg kihívsz a vakvilágba és módosítgatsz dolgokat. De mindegy, nem itt van a bottleneck, ezt már többen elmondtuk neked.

" csináljon egy SELECT-et, amire megadtam két szűrési feltételt"

Hanem pont itt: az egyik szűrési feltétel egy tárolt eljárás, amivel nem tud mit kezdeni. Ha a tároltban lévő feltételt átírnád sima Queryre (és vannak normálisan indexek, amik alapján tud a planner okosítani), akkor az RDBMS-nek van esélye arra, hogy ne sima szekvenciális olvasást csináljon, hanem valami értelmesebb dolgot helyette. De nem tud. Ezt kellene már megértened.

És mivel nincs semmi infója a query plannernek, hogy mi történik a tárolt eljáráson belül, milyen szűrést kellene végezni ott, stb. ezért nem tud mit csinálni, mint felnyalja az egész táblát és az összes rekordra lefuttatja azt. Egyesével. Rekordonként. Mert _TE_ nem hagysz neki más lehetőséget.

Többször nem írom le, hogy ez a probléma.

Ez benne van az explainben, nem tudok már mit mondani, tanuld meg értelmezni. (Ebben egyébként szerintem sokat segit a PgAdmin III grafikus explainje is (F7). Ill van a PgAdmin 4-ben is olyan, csak az meg ilyen félwebes szar és rühellem, de abban is van.

"AZ HOGY FELÜLBÍRÁLJA, FIGYELMEN KÍVÜL HAGYJA AZ ELFOGADHATATLAN, HIBÁS MŰKÖDÉS!"

Elárulok egy titkot: az RDBMS-ek plannerjei meglehetősen rendszerint figyelmen kívül hagyják, hogy _hogyan_ írtad meg a queryket. Attól függően, hogy milyen statisztikái vannak, mennyi adat van a táblákban, stb. figyelmen kívül hagyhatnak pl. olyan dolgokat, hogy van-e index vagy sem (mert mondjuk egy index indirekció szerinte még mindig lassabb lenne, mint egy seq scan egy kis tábla esetén) vagy, hogy mondjuk egy WHERE záradékot esetleg úgy oldja meg, hogy negálja neked a feltételt mert úgy tud indexet használni és inkább rekordokat kizár az összes közül és nem pedig kiválasztja a feltételnek megfelelőket az tök normális. Mert te egy SQL Queryben azt írod le, hogy mit szeretnél kapni eredményül, nem pedig azt írod le, hogy mit csináljon az RDBMS. Más paradigma, mint a procedurális programozás, mondtam már.

De mondom még egyszer: jelen esetben _NEM_ ez a szűk keresztmetszet.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

A hozzászólás, amire válaszolsz, fejlődött, miközben megírtam. A fejlődés iránya az volt, hogy közben megértettem. Van gy olyan érzésem hogy ez nem jött le.
Az számomra kicsit nehezen emészthető, hogy az SQL, ha pl. hazudok, azt figyelmen kívül hagyja. Szoktam a C++ -ban is. Van a projektben olyan metódus, ami per definit nem konstans, én pedig azt állítom róla, és ez nem hiba, és a fordító simán elhiszi, ez a dolga. Mindegy, az SQL így működik, bizonyára okkal, még csak értenem sem szükséges, csak tudomásul venni.
Bár végig az az érzésem, hogy nem nagyon értette meg senki, hogy pontosan mit nem értek, mit gondolok máshogy/rosszul, azért elég jól körbelőttétek, így nehezen, de megértettem.
És mindenkinek köszönöm, aki időt fordított a köd eloszlatására. Az SQL ezután sem lesz a barátom, de jóval kevesebbet fogok kinézni bután a fejemből, ha hülyeségeket találok leírni egy query ürügyén.
Ui.: A min_shard(...) -et végül átírtam feltételes kifejezésre, de elég volt a RAISE INFO-t és az error(...) hívást kivenni, hogy a lekérdezés lefusson 22 mSec alatt.

Elolvastam, sokadszorra, nem segít.
A megjegyzésben írtaknak viszont, nem tudok másra gondolni, lehet az a következménye, hogy felülbírálja az általam odabiggyesztett kategóriát, de azért ez pontosan nincs leírva, és szerintem ez egy inkorrekt működés, ha szigorkodik, és nem tetszik valami, dobjon kizárást, és ne javítgassa a kódomat.
Nem az IMMUTABLE-t értettem félre. Túltoltam a program kód értelmezéséről vallott elveimet.

Az IMMUTABLE csak annyit jelent hogy ugyanolyan bemenetre mindig ugyanolyan kimenetet fog adni, és ezt garantálod. Az ellen nem véd, hogy neki
végig kell mennie a teljes táblán, hogy kiolvassa az értékeket, amivel meghívja a függvényt, maximum a függvény result-ja cache-ből fog visszajönni.

"An IMMUTABLE function cannot modify the database and is guaranteed to return the same results given the same arguments forever. "

Ok. Csakhogy nem arról van (főleg) szó, hogy mit jelent az IMMUTABLE. A lényeg és amin volt szerencsém kiakadni, az az, hogy mit kezd azzal, hogy én valamit állítok, oda írok a kódba, akkor: tudomásul veszi, leszarja/felülbírálja, vagy hibát dob.
A másik probléma pedig az volt, hogy én egy kicsit máshogy gondoltam egy SELECT végrehajtását, amolyan algoritmikusan. Ha két SELECT-et írok le, az nem két SELECT, csak le lehet írni így is. Márpedig az egy és a kettő más-más algoritmus, és ami az egyikben triviális, a másikban akár hibának is tűnhet.
Nem elég ahhoz érteni, amiről kérdeznek, azt sem árt megérteni hogy mit nem ért, vagy gondol máshogy a kérdező. Mert különben csak hosszú lére eresztett egymás mellet elbeszélés lesz.

Na de neked egy SELECT-ben egy VIEW-ed van, ami két külön SELECT-ből áll össze. Ezt először elő is kell állítania az RDBMS-nek. (Ezt egyébként négy lépésben tudja: először megcsinálja a selectet + WHERE-t rá, utána a másik SELECT-et és a WHERE-t rá MIVEL optimálisabb először szűrni és utána már a kisebb adatmennyiséget APPEND-elni majd SORT-olni, UNIQE-olni - főleg, hogy erre még pontenciálisan indexet is tudna használni, ami az összevont adatmennyiségre nincs meg.

Persze lehet úgy is, hogy algoritmikusan végrehajtja ugyanazt, amit te leírsz szó szerint, csak az lassabb lesz.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Csakhogy itt két WHERE van, az elsőnek az eredménye az a 0-2 rekord (max 4), és ez nem tartalmazza a min_shared() függvényhívást, amit viszont mégis meghív minden phs_links rekordra.
Ezért bontottam a lekérdezést két részre, de jól láthatóan feleslegesen, mer az SQL szerver "okosabb".
Én nagyon hiányolom az SQL-bőn a C-ben nagyon hasznos || és && operátorokat, mert így rengeteg kifejezést (kifejezés részletet) kell feleslegesen kifejteni (vagy bonyolultabban leírni), mivel annak értéke már irreleváns.
Ja, és az első WHERE-ben szereplő feltételben a port_id2 index, és része a phs_link_type2 -vel együtt egy egyedi kulcsnak.

Ezt, most egy "Beküldés" bökésre, miért rakta be kétszer? (törölni persze nem lehet, ami szerintem nem felel meg a GDPR-nek)

( haspokember | 2018. 10. 31., sze – 09:13 )

WHERE min_shared(plr.port_shared, ?::portshare) <> 'NC'::portshare

Szerintem ez nem SARGable (a min_shared az egy fuggveny, ugye?). Ezert hajtja vegre a szerencsetlen szerver ezt a fuggvenyt annyiszor, mert maskent nem tudja eldonteni, hogy a felteteled igaz-e az adott rekordokra, nem birja indexbol kiszedni. Szerintem ezt a feltetelt kene atfogalmazni ugy, hogy SARGable legyen. Pl. GROUP BY, MIN() elso tippre, bar nem egeszen ertem ezt a :: szintakszist.

Amugy az indexeid rendben vannak?

(Az elso SELECT meg egy sima JOIN-ra konvertalhato, az adatbazis is ezt teszi.)

"(Az elso SELECT meg egy sima JOIN-ra konvertalhato, az adatbazis is ezt teszi.)"
Hát pont ez a probléma. Hogyan lehet rávenni, hogy ne tegye? Én is le tudtam volna írni simán csak JOIN-okkal, de nem tettem, pont azért, mert sejtettem, hogy a min_shared()-et orba-szájba hívogatni fogja.
A min_shared() -et kétlem, hogy át tudnám fogalmazni a fenti feltételnek megfelelően, arról nem is beszélve, hogy már így is két helyen van leírva ugyan az (SQL és C++), ha leírom harmadik módon is, akkor a kétszeres hibalehetőségből máris háromszoros lesz. Ebben az 50-60e sorban van már így is éppen elég lehetőségem.

EXPLAIN-ből látszik, hogy az a legnagyobb költség, amikor szekvenciálisan végigolvassa először phs_links_table-t KÉTSZER, hogy appendelje, majd megetesse vele a függvényed. Aztán majd még hozzá is joinolod önmagához.

Itt már eleve probléma az, hogy egy resultsetet kell joinolnia (mennyire kell konzisztencia? Mennyire változók az adatok? Tudnál a phs_links_table táblából materialized view-et csinálni, arra indexet tenni.

Mondjuk valószínűleg kénytelen leszel a függvényt is kidobni és a WHERE-be beleírni, mert akkor nem kell az összes adatot (3x) felnyalni a lemezről.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

( mrceeka v | 2018. 10. 31., sze – 11:01 )

Milyen sűrűn kerül futtatásra ez a lekérdezés? Production rendszer ez vagy valami tanulás? Ha production, mi a hatása ennek a lassúságnak és hány százaléka a teljes PostgreSQL CPU-nak és IO-nak ez a lekérdezés?

Üdv,
Marci

A programrendszerben (https://github.com/csikfer/lanview2) egy a GUI-ból indítható funkció. Hiányzó, de kikövetkeztethető összeköttetéseket keres a switch-ek címtáblái alapján. A júzer kiválasz egy patch panelt/fali csatlakozót, megnyomja a start gombot és a SELECT lefut annyiszor, ahány port van. Ha percekig tart, az elég kényelmetlen.
Nekem production, mivel rendszeresen használom, erre mást nem igazán sikerül rávenni.
Az egyébként érdekes, hogy programból legalább egy-két nagyságrenddel gyorsabb a lekérdezés, mint a pgAdmin III-ból.