[Postgres] Tabel sorteren

[QuarK]

Is het mogelijk om een tabel in een PostgreSQL database te sorteren aan de hand van 1 van de kolommen? Ik bedoel geen ORDER BY clausule in een query, maar de tabel optimaliseren door entries in de goede volgorde neer te zetten ipv door elkaar heen wat het nu dus is.


PS Ik ben niet zeker of P&W wel de goede plaats is, maar in NOS en NT is hij ook niet echt op z'n plaats. SA zou ook kunnen

[thomaske]

Ik weet niet precies wat je wilt dat er gebeurt, maar wanneer je een index op een bepaalde kolom zet, kan je sneller en efficienter op deze kolom selecteren..

even op zoek naar 'index' dus

[QuarK]

Ik gebruik natuurlijk ook indices, maar ik heb het gevoel dat inserts een stuk langzamer gaan door het updaten van die gigantische index (het is een tabel met erg veel records).

[Bosmonster]

QuarK schreef op 10 December 2002 @ 14:04:
Ik gebruik natuurlijk ook indices, maar ik heb het gevoel dat inserts een stuk langzamer gaan door het updaten van die gigantische index (het is een tabel met erg veel records).

De volgorde maakt voor de DB niet uit.. Hij voegt de record toch aan het einde in en update daarna de indices..

Het enige nadeel van een index is dan ook dat je updates/inserts/deletes iets meer tijd kosten, terwijl je selects er heel veel rapper van worden. En wat doe je nu het meest

[ Voor 3% gewijzigd door Bosmonster op 10-12-2002 14:07 ]

[whoami]

Indexen vertragen het inserten/updaten/deleten idd, maar als je je tabel sorteert zal dat de performance van de select-queries zeker niet verhogen.

[QuarK]

Wat ik bedoel is dat het updaten van die index steeds langer lijkt te duren (meer dan evenredig met de lengte van de tabel).
Daarom wil ik eens gaan kijken of het sneller gaat als de tabel netjes is gesorteerd.

Weet iemand een nette methode? Of zowieso een werkende methode?

[Glimi]

Dat heet vacuümen in postgreSQL en wordt TEN STELLIGSTE aangeraden. Liefst elke dag ff doen

edit:
Dan begrijp ik je helemaal verkeerd Excuus

[ Voor 27% gewijzigd door Glimi op 10-12-2002 14:16 ]

[Freee!!]

Misschien een ideetje:
Rename de tabel, maak de gewenste tabel opnieuw leeg aan en vul deze vanuit de oude tabel in de gewenste volgorde (insert into <tabel> select from <hernoemde tabel> order by <gewenste kolom(men) zou het moeten doen). Natuurlijk moet je daarna nog je indices overzetten.

[QuarK]

Glimi schreef op 10 December 2002 @ 14:10:
Dat heet vacuümen in postgreSQL en wordt TEN STELLIGSTE aangeraden. Liefst elke dag ff doen

De vacuum gebeurt elke nacht, en sorteert de boel *niet*.
Het ruimte verwijderde records op, analyseert de boel een beetje.

[Bosmonster]

Glimi schreef op 10 December 2002 @ 14:10:
Dat heet vacuümen in postgreSQL en wordt TEN STELLIGSTE aangeraden. Liefst elke dag ff doen

Het wordt afgeraden, maar je moet het het liefst elke dag ff doen

[QuarK]

Mr. Liu schreef op 10 December 2002 @ 14:13:
Misschien een ideetje:
Rename de tabel, maak de gewenste tabel opnieuw leeg aan en vul deze vanuit de oude tabel in de gewenste volgorde (insert into <tabel> select from <hernoemde tabel> order by <gewenste kolom(men) zou het moeten doen). Natuurlijk moet je daarna nog je indices overzetten.

Dat zou best een werkende methode kunnen zijn.
Als er niets ingebouwd in PSQL zit dan klus ik daar wel iets mee.

[sebas]

Wat ook zeer handig is als je vaak sorteert is het verhogen van de shared memory, vooral de hoeveelheid geheugen die voor sorts ter beschikking staat kan de boel nogal vertragen, verhoog dit en je queries zullen heel wat sneller worden. Meer info daarover kun je bijvoorbeeld onder de volgende link vinden:

http://www.phpbuilder.com/columns/smith20010821.php3?page=2

Voor de rest natuulijk even goed doorkijken op welke tables en columns je indices gebruikt en of je daar nog wat aan kunt sleutelen.

[QuarK]

Hee dat is een leuk artikeltje.. even lezen.
Verder is de keuze voor indices wel in orde, alle queries zijn uitgebreid getest.

[jochemd]

code:

CLUSTER table ON index;

Maar ik zou eerst eens gaan kijken naar je VACUUM settings. Draai je VACUUM of VACUUM FULL ANALYZE? Doe eens de VERBOSE versie en kijk eens hoe groot alles is, heb je niet gewoon idioot veel/grote indexes? Kijk dan naar triggers, foreign keys, rules en constraints, het is heel wat waarschijnlijker dat die de zaak vertragen dan een index.
Pas als je dat allemaal gedaan hebt zou ik gaan denken aan CLUSTER (waarmee je in principe 1 specifiek query-patroon kan versnellen en je er voor moet zorgen dat dat dus het query patroon is dat in je constraints/FK's wordt gebruikt).

Controleer de handleiding!!! CLUSTER heeft bijwerkingen in oudere versies van PostgreSQL.

[ Voor 14% gewijzigd door jochemd op 10-12-2002 16:00 . Reden: waarschuwing toegevoegd ]

[ACM]

QuarK schreef op 10 december 2002 @ 14:00:
Is het mogelijk om een tabel in een PostgreSQL database te sorteren aan de hand van 1 van de kolommen? Ik bedoel geen ORDER BY clausule in een query, maar de tabel optimaliseren door entries in de goede volgorde neer te zetten ipv door elkaar heen wat het nu dus is.

Dmv cluster gebeurt dat, let op de bijwerkingen en ikzelf heb er nooit echt voordelen uit weten te halen.

Zoals ook al gezegd zullen primairy keys, (functionele) indices, triggers en (die vooral bij postgres) foreign key-constraints flink uit kunnen maken.

Wat je dan es moet testen is, als je alle constraints en extra indices uit hebt staan (alleen de primairy key dus) of het dan nog steeds zo langzaam gaat, dan de indices weer aan maar de foreign-key's nog niet etc.

QuarK schreef op 10 december 2002 @ 14:13:
De vacuum gebeurt elke nacht, en sorteert de boel *niet*.
Het ruimte verwijderde records op, analyseert de boel een beetje.

Vacuum full analyze zal iig een verbetering van de performance opleveren, er worden blocken samengepakt etc etc.
De boel wordt idd niet gesorteerd meen ik (ik geloof wel dat de meest gebruikte records vooraan gezet worden?)

Bosmonster schreef op 10 december 2002 @ 14:14:
Het wordt afgeraden, maar je moet het het liefst elke dag ff doen

aangeraden staat er

sebas schreef op 10 december 2002 @ 14:20:
Wat ook zeer handig is als je vaak sorteert is het verhogen van de shared memory, vooral de hoeveelheid geheugen die voor sorts ter beschikking staat kan de boel nogal vertragen, verhoog dit en je queries zullen heel wat sneller worden. Meer info daarover kun je bijvoorbeeld onder de volgende link vinden:

Dat heeft vooral allemaal impact op selects lijkt me en hoewel de insert natuurlijk ook allerlei index-checks doet lijkt me dat toch minder relevant.

[QuarK]

Ik draai versie 7.2.1 (die van woody).
Ik kan zo snel geen document vinden over bijwerkingen van cluster, dus ik ga er maar vanuit dat het safe is in deze versie

Dit was idd waar ik naar op zoek was. Ik wil het eenmalig draaien, echt niet elke nacht ofzo.

En ik doe zowieso altijd een VACUUM FULL ANALYZE.

[ACM]

QuarK schreef op 10 december 2002 @ 16:20:
Ik draai versie 7.2.1 (die van woody).
Ik kan zo snel geen document vinden over bijwerkingen van cluster, dus ik ga er maar vanuit dat het safe is in deze versie

Dit was idd waar ik naar op zoek was. Ik wil het eenmalig draaien, echt niet elke nacht ofzo.

En ik doe zowieso altijd een VACUUM FULL ANALYZE.

http://www.postgresql.org/idocs/index.php?sql-cluster.html

onderaan.

Just a quick reminder:
Since CLUSTER creates a new table from your re-ordered data, all other indexs and referential integrity triggers are dropped.

Met pg-7.3 was dat opgelost meen ik.

[QuarK]

Ja ik zag net dat hij die indices heeft gedropt, moest ze opnieuw aanmaken.
Is niet zo'n groot probleem voor deze eenmalige actie.

Bedankt voor je hulp!

[jochemd]

ACM schreef op 10 December 2002 @ 16:17:

De boel wordt idd niet gesorteerd meen ik (ik geloof wel dat de meest gebruikte records vooraan gezet worden?)

PostgreSQL weet niet eens wat de meest gebruikte records zijn.

Met pg-7.3 was dat opgelost meen ik.

Fix CLUSTER to preserve all table attributes (Alvaro Herrera)

[ACM]

jochemd schreef op 10 December 2002 @ 23:33:
PostgreSQL weet niet eens wat de meest gebruikte records zijn.

Wel als je de stats collector aanzet
Na een analyze staan dan in de pg_stat* tabellen allerlei zinvolle dingen voor de query-analyser, en ook de meest voorkomende records (kwa inhoud) en ik meen ook de meest gequeriede records.

[ Voor 8% gewijzigd door ACM op 11-12-2002 00:15 ]

[jochemd]

ACM schreef op 11 december 2002 @ 00:14:

Wel als je de stats collector aanzet
Na een analyze staan dan in de pg_stat* tabellen allerlei zinvolle dingen voor de query-analyser, en ook de meest voorkomende records (kwa inhoud) en ik meen ook de meest gequeriede records.

Ja/nee. De stats collector logt de activiteit van de database, maar gebruikt daarvoor geen analyze maar gewoon de gedraaide queries. Er wordt dus geen informatie over over de meest voorkomende waardes en de distributie van die waardes in een tabel opgeslagen (dat doet analyze en wordt opgeslagen in de pg_statistic tabel).
Alleen moet je de stats collector wel configureren, en mensen die dat doen hoeven niet te vragen hoe dat commando om tabellen te sorteren heet.

[ACM]

Ah, ik zie dat waar het 'mis' ging, ik gebruikte een foute formulering.
Ik bedoelde idd "meest gebruikte" en niet "meest voorkomende".