PostgreSQL - Uitvoer kolommen van stored procedure

[Priet]

In PostgreSQL heb ik een Stored Procedure gemaakt:

SQL:

CREATE FUNCTION sp_test(arg_category_id INTEGER)

De SP geeft een aantal resultaten terug die voldoen aan category_id. Nu wil ik deze SP aanroepen voor een bepaald aantal category_id.

SQL:

SELECT  sp_test(C.id)
FROM    categories C
WHERE   C.active = TRUE

De SP wordt uitgevoerd in ongeveer 1,8 seconden (prima). Maar de kolommen van de SP worden als één kolom teruggeven:

sp_test
-------
(a,b,c)
(d,e,f)

Waar ik juist deze uitvoer wil:

kolom1 | kolom2 | kolom3
------------------------
a      | b      | c
d      | e      | f

Dat zou ik kunnen bereiken door de query als volgt aan te passen:

SQL:

SELECT  (sp_test(C.id)).*
FROM    categories C
WHERE   C.active = TRUE

Maar dan duurt m'n query maar liefst 7 seconden!

Waarom duurt dit zo lang? Kan ik op een andere manier de SP uitvoeren voor de verzameling category_id?

[doskabouter]

En wat gebeurt er als je je (...).* in een omsluitende query doet?
bv:

SQL:

SELECT (foo),* FROM (
SELECT  sp_test(C.id) AS foo
FROM    categories C
WHERE   C.active = TRUE
) AS bar

heeft ie dan dezelfde tijden/query plans?

Let overigens wel op dat caching je gemeten tijden behoorlijk verwarrend kan maken...

[Priet]

Die oplossing (met een extra SELECT/FROM) zorgt voor dezelfde tijden als mijn eerste query (1.8 seconden). En het geeft netjes alle kolommen terug, dus dat is heel fijn

Blijf ik het toch raar vinden waarom (..).* zoveel trager werkt?

[doskabouter]

Goede vraag.
Misschien heeft postgres de (in)efficientie van die functie niet helemaal goed in de gaten en trekt ie het verkeerde plan?

Verder zou ik het zo niet weten, maar fijn dat het probleem in ieder geval opgelost is

[Gomez12]

Waar ik vermoed dat het fout gaat is dat jouw volgorde omgekeerd is aan wat de optimizer standaard wil...

Standaard zal die eerst functies binnen haakjes willen uitvoeren en daarna pas de where eroverheen gooien.
Terwijl jij expliciet eerst de where limitatie wilt hebben en daarna pas de functie.

Ik vermoed dat het voornamelijk is doordat je wel een PK op ID hebt staan, maar geen index op ID + active. Waardoor je functie over een PK heel cheap lijkt om te verwerken.

[The Eagle]

Waarom een stored procedure? Volgens mij kun je veel beter met een (complex, want function based) view aan de slag. Een function based view is in feite ook een opgeslagen query, maar wordt nders door de optimizer behandeld en je kunt er dus ook veel beter mee tunen. En als jij dan evt ook nog een function based index aan je tabel toe kunt voegen met de juiste velden, wordt ie helemaal rap

[storeman]

Ik vermoed dat het iets te maken heeft met het uitpakken van de records, wat per rij moet gebeuren ipv bulk (sorry, duidelijk kan ik het niet maken, omdat ik het eigenlijk ook niet weet).

Wat altijd heel verhelderend is, is je query eens te draaien met "EXPLAIN" ervoor, dan zie je meestal vrij snel wat de beperkende factor is.

[storeman]

The Eagle schreef op maandag 01 februari 2016 @ 22:00:
Waarom een stored procedure? Volgens mij kun je veel beter met een (complex, want function based) view aan de slag. Een function based view is in feite ook een opgeslagen query, maar wordt nders door de optimizer behandeld en je kunt er dus ook veel beter mee tunen. En als jij dan evt ook nog een function based index aan je tabel toe kunt voegen met de juiste velden, wordt ie helemaal rap

Wat is het verschil? Zoals de TS het laat zien, is het in Postgres volgens mij gewoon een functie, waar je inderdaad ook een index op kan zetten, maar dat is alleen relevant als je gaat filteren op de data uit de functie, niet als je de data zelf wilt hebben volgens mij.

[The Eagle]

Psst, als je als laatste gereageerd heb, edit dan je post ipv een nieuwe reactie

En met explain plans kun je idd heel veel zien.
Laat ik het zo zeggen: het zou me niks verbazen als er in dat plan full table scans voorkomen.

Maar je hebt wel een punt. Want zonder te weten wat er exact in de functie zit valt er geen pijl te trekken op wat evt sneller kan zijn.
@TS: geef eens wat meer info over de SP. Wat doet dat ding exact?

[Gomez12]

storeman schreef op maandag 01 februari 2016 @ 22:03:
[...]
Wat is het verschil? Zoals de TS het laat zien, is het in Postgres volgens mij gewoon een functie, waar je inderdaad ook een index op kan zetten, maar dat is alleen relevant als je gaat filteren op de data uit de functie, niet als je de data zelf wilt hebben volgens mij.

Ik heb geen idee of een complex view beter door de optimizer komt, maar SP's / eigen functions vereisen bijna altijd wat handmatig geoptimaliseer in bijna elke RDBMS want de optimizer kan heel slecht omgaan met functies / SP's omdat die van alles kunnen bevatten en geen vaste costs heeft.

Een optimizer is in de basis een vrij simpel ding (let op : in de basis) wat gewoon wat costs berekent over een x aantal vaste statements en wat statistics over tabellen / indexen. Dat is vrij simpel te doen.
Echter met SP's heeft dat een potentie om veel en veel en veel complexer te worden (ik ken postgresql niet echt, maar met mssql kan je bij een row a direct returnen en met een row b pi tot oneindig laten uitrekenen) daar kan een optimizer geen rekening mee houden in de beperkte tijd dat die draait.

Oftewel meeste optimizers hanteren gewoon vaste kosten voor een SP en die zijn bijna nooit correct

[Cynosura]

Normaal zou je de functie zo moeten aanroepen:

SQL:

SELECT * FROM sp_test(123)

Maar ik weet even niet hoe je het dan voor elkaar krijgt om id's uit een andere tabel als invoer te nemen.

Zo dus:

SQL:

SELECT (record).* FROM (SELECT sp_test(id) AS record FROM categories WHERE active = TRUE) AS x

of nog korter:

SQL:

SELECT (sp_test(id)).* FROM categories WHERE active = TRUE

En dat lijkt wel op wat doskabouter al zei.

[ Voor 44% gewijzigd door Cynosura op 04-02-2016 23:16 ]

[cariolive23]

In welke taal heb je de SP geschreven? SQL, PL/pgSQL, PL/Perl, etc. ? Dit kan uitmaken omdat met een procedure in SQL er wil "dieper" kan worden gekeken. Ook in de andere talen maakt het uit wat je doet en hoe je het doet. Daarnaast kun je nog de IMMUTABLE | STABLE | VOLATILE kiezen en de COST en het aantal ROWS voor de SP dat wordt teruggeven. En dat alles naast nog een flink aantal andere parameters die je kunt instellen... Dit alles heeft allemaal invloed op de overall performance.

http://www.postgresql.org...e/sql-createfunction.html