[Postgres] optimaliseren crosstab views

[Verwijderd]

Op internet zijn er veel technieken te vinden, maar ik weet niet welke er bij mijn situatie past. Misschien is er iemand die meer ervaring heeft met crosstabs en weet of ik de goede kant op ga...

Achtergrond:
Het onderhoud van databases vond ik nogal veel werk, omdat het aantal toenam naarmate je meer applicaties (of sites met CMS) ging bouwen. Daarnaast was de database inrichting vaak hetzelfde. Ik dacht aan een centrale database met daarin alle sites en één backend zodat gebruikers hun content kunnen bewerken. Daarbij wilde ik crosstabs gebruiken, zodat je voor templates direct velden (titels, tekst, datums, images, etc.) kon aanmaken zonder je database tabellen moest aanpassen of aanmaken. Ik vond hier een manier om dynamische views te maken, waar ik echt veel aan heb gehad. Deze basis is echt perfect om dynamische applicaties of websites te bouwen. Systeem: Postgres 8.1, PHP 5.2, Apache 1.3

Probleem:
Bij complexe queries (bv. financiele berekeningen), waarbij ik meerder views JOIN, dan duurt een query ongeveer 3 tot 10 sec. De tabel bevat nu ongeveer 30.000 records. Het gevolg is dat de performance waarschijnlijk slechter wordt bij toename van de records (en users). De views moeten dus geoptimaliseerd worden, maar hoe?

Onderzoek:
Er zijn volgens mij een aantal mogelijkheden, die behoorlijk veel tijd kosten om te implementeren en testen. Ik weet niet of dit de moeite waard is en of het sneller wordt:

1. Postgres 8.3 crosstab function gebruiken
2. De huidige dynamische views omzetten naar Materialized views
3. Postgres 8.3 UUID indexen gebruiken i.p.v. char(32)

Wat denk jij wat ik het beste kan doen?

[Kettrick]

Ik zou om te beginnen eens aan de slag gaan met explain analyse

Een view is niets meer dan een voorgedefineerde query, die kan je dus gewoon op de normale manier explainen.

Mogelijk kan je het geheel behoorlijk versnellen door een paar indexes aan te maken of aan te passen.

[leuk_he]

Als ik je goed begrijp wordt dus elke keer als je de view benadered een full table scan(/group by) op een tabel van 30.000x30.000x.... records gedaan?

-ligt er een goede index op het veld waarop je je joint?
-Heb je elke keer 30.000 records nodig of kun je in de applicatie nog beter filteren?

affijn.. traditioneel bekijken met een anlyze wat de database doet lijkt me het probleem beter duidelijk te krijgen

explain plan dus:

http://www.postgresonline...ain-Plans.html#trackbacks

crosstab functie is hierbij een grote win, maar maakt het wel complexer, maar bij grotere aantallen joins zeker een win.

Volgens mij leveren UUID indexen een % gewin op, terwijk je een log(n) gewin wilt hebben. Ik vermoed dat deze winst ralatief klein is.

materialize views kan ik niet inschatten, ligt erg aan de aard van je data (materialize views zijn vertraagd/oude data of verschuiven de werkload naar insert)

[Verwijderd]

leuk_he bedankt!

Ik besef me opeens dat er inderdaad een property_parent veld is die geen index heeft, eens kijken of dat scheelt.

Wat betreft de 30.000 records: er is een tabel content met 10.000 records (rijen). Een andere tabel heeft 30.000 records met content_properties (cellen). En er is een tabel property_title voor de kolom titels. Bij de select * from view gebruik ik wel een WHERE om de content te filteren op bijvoorbeeld page_id. Ik weet niet of hij dan evengoed 10.000 x 30.000 doet en dan filtert of bijvoorbeeld 500 x 30.000. Ik ga weer met die explain analyse aan de slag, bedankt voor de link.

Ik dacht dat die crosstab functie dezelfde opbouw zou hebben als die ik nu gebruik. Hier een definitie van een view:

SQL:

SELECT phc.page_id, c.content_id AS voorraadartikel_id, c.content_title AS voorraadartikel_title, c.content_create AS voorraadartikel_create, c.content_change AS voorraadartikel_change, c.content_publish AS voorraadartikel_publish, c.content_order AS voorraadartikel_order, max(
        CASE
            WHEN cp.property_id = '76a16b233d0a2468af3b6a957f0a33ad'::bpchar THEN ( SELECT property.property_name
               FROM property
              WHERE property.property_id = cp.property_parent)
            ELSE NULL::character varying
        END::text) AS artikeltype, bool_or(
        CASE
            WHEN cp.property_id = 'd6a31d363889ebd7c68dcccd30730f66'::bpchar THEN true
            ELSE false
        END) AS verkoop_item, max(
        CASE
            WHEN cp.property_id = '27b10892614352a773ac10d534c3d0cf'::bpchar THEN cp.property_numeric
            ELSE NULL::numeric
        END) AS lengte, max(
        CASE
            WHEN cp.property_id = '438b35e5019bdc6265e07b8d0a3c1947'::bpchar THEN cp.property_numeric
            ELSE NULL::numeric
        END) AS aantal_magazijn, max(
        CASE
            WHEN cp.property_id = '797547598c816df937a4c85310801eae'::bpchar THEN cp.property_numeric
            ELSE NULL::numeric
        END) AS gewicht, max(
        CASE
            WHEN cp.property_id = 'a3d7d2e0106ae00aff4a2d3accbf3b44'::bpchar THEN cp.property_numeric
            ELSE NULL::numeric
        END) AS prijs, max(
        CASE
            WHEN cp.property_id = 'c0eeabf8832266aeb21a31b4c1fe8c1b'::bpchar THEN cp.property_description
            ELSE NULL::character varying
        END::text) AS bijzonderheden
   FROM pagehascontent phc
   JOIN content c USING (content_id)
   LEFT JOIN contentproperty cp USING (content_id)
   LEFT JOIN property p ON p.property_id = cp.property_id
  WHERE c.contenttemplate_id = '7dd361f0b71125b74f80c39d49fa9404'::bpchar AND c.content_delete IS FALSE
  GROUP BY phc.page_id, c.content_id, c.content_title, c.content_publish, c.content_create, c.content_change, c.content_order
  ORDER BY c.content_order DESC;

Die MAX en CASE lijkt me de boel trager te maken. Ik ga in een nieuwe versie toch eens Postgres crosstab gebruiken.

Ik ben weer een stap verder!

[cariolive23]

Wat is het resultaat van de EXPLAIN? Zonder die gegevens kunnen we alleen maar raden waarom de query langzaam is. Wellicht zijn er indexen die helemaal niet worden gebruikt of niet efficient zijn. Een aanpassing in de indexen kan dan ook enorme verbetering laten zien.

[justmental]

Ik zie een group by in je view, het lijkt me sterk dat postgres predicates een view in kan pushen waar een group by in zit.
Dat betekent dus dat elke query op deze view de complete view opbouwt zonder enige filtering.

[leuk_he]

justmental schreef op vrijdag 09 januari 2009 @ 21:34:
Ik zie een group by in je view, het lijkt me sterk dat postgres predicates een view in kan pushen waar een group by in zit.
Dat betekent dus dat elke query op deze view de complete view opbouwt zonder enige filtering.

ALs je in index op pageid hebt zou dat toch juist wel moeten kunnen?

[Verwijderd]

@cariolive23 dit is het resultaat van de planner:

SQL:

EXPLAIN SELECT * FROM voorraadartikel WHERE page_id = '7d0592ee7ff3636f11026a9e7f9899ce':
Sort  (cost=624.69..624.73 rows=16 width=262)

  Sort Key: c.content_order

  ->  HashAggregate  (cost=528.91..624.37 rows=16 width=262)

        ->  Nested Loop Left Join  (cost=130.39..528.35 rows=16 width=262)

              ->  Hash Join  (cost=130.39..436.13 rows=3 width=157)

                    Hash Cond: ("outer".content_id = "inner".content_id)

                    ->  Bitmap Heap Scan on content c  (cost=6.63..311.46 rows=177 width=85)

                          Recheck Cond: (contenttemplate_id = '7dd361f0b71125b74f80c39d49fa9404'::bpchar)

                          Filter: (content_delete IS FALSE)

                          ->  Bitmap Index Scan on templatecontent_fk  (cost=0.00..6.63 rows=181 width=0)

                                Index Cond: (contenttemplate_id = '7dd361f0b71125b74f80c39d49fa9404'::bpchar)

                    ->  Hash  (cost=123.38..123.38 rows=151 width=72)

                          ->  Bitmap Heap Scan on pagehascontent phc  (cost=3.53..123.38 rows=151 width=72)

                                Recheck Cond: (page_id = '7d0592ee7ff3636f11026a9e7f9899ce'::bpchar)

                                ->  Bitmap Index Scan on pagehascontent_fk  (cost=0.00..3.53 rows=151 width=0)

                                      Index Cond: (page_id = '7d0592ee7ff3636f11026a9e7f9899ce'::bpchar)

              ->  Index Scan using contentproperty_fk on contentproperty cp  (cost=0.00..30.64 rows=8 width=177)

                    Index Cond: ("outer".content_id = cp.content_id)

        SubPlan

          ->  Index Scan using property_pk on property  (cost=0.00..5.92 rows=1 width=15)

                Index Cond: (property_id = $0)

@justmental: ik filter dus page_id, zie regel 32. Of moet de WHERE page_id binnen de view definitie vallen? Zal dat meer snelheid opleveren?

[voodooless]

Wat je sowieso als eerste moet doen is een analyze op een tabel. Dit zorgt ervoor dat postgres de tabel gaat bekijken, en vast gaat stellen hoe hij bepaalde queries het snelste kan doen. Dit is echter ook sterk afhankelijk van de settings in postgresql.conf. Check dus even goed hoe deze in elkaar steken. Er zijn diverse howto's over te vinden, maar helaas is het grotendeels toch nog trail en error.

Als je dit gedaan hebt kun je gaan kijken naar de explain. Daar kun je dan zien waar de bottenek zit. In pgadmin kun je zelfs de explain visueel zien. Vaak is dat wat makkelijker te doorzien.

Kijk ook naar de indexen. Zorg dat je die aanmaakt waar nodig, en vergeet vooral niet een analyze te doen na het aanmaken, anders wordt de index niet gebruikt!

Het kan ook voorkomen dat de index toch niet gebruikt wordt ondanks dat die er wel is. Vaak is dat een probleem met de configuratie. Postgres vind dan dat het niet gebruiken van de index sneller is dan het wel gebruiken. Vaak is dat niet waar. Je kunt echter het gebruik van indexen forceren door een seqence scan te disablen.

SQL:

set enable_seqscan=false

Let wel op dat dit dan voor alle queries na deze geldt.

Natuurlijk kun je ook nog kijken naar de query zelf. Daar valt vaak ook nog wel wat winst te halen.

[leuk_he]

-> Hash Join (cost=130.39..436.13 rows=3 width=157)

Hash Cond: ("outer".content_id = "inner".content_id)

/edit3:Ja, die hash join is eht meest verdacht. Kijk die contentid nog maar na, al heb ik zondermeer altijd al een hekel aan outer joins omdat je dan nulls in het het resultaat introduceert met alle verrassingen van dien.

Zit er goed indexen content_id? zo ja kijk de opmerkingen van voodoless eens na?

/edit:

probeer maar te verplaatsen van de page_id naar de view, volgens mij levert dat een zelfde resultaat in de planner op.

/edit2:
Bitmap indexen. nee toch? Of geeft de planenr dat gewoon zo weer..


PS, we hoeven niet heel de database of alle execution plans hier te hebben. We willen en kunnen het toch niet naspelen.

[ Voor 56% gewijzigd door leuk_he op 11-01-2009 20:56 ]

[Verwijderd]

Op de content_id en page_id zit een UNIQUE INDEX. Dit is toch voldoende?

Ik heb voor property_parent een INDEX gemaakt met WHERE property_parent IS NOT NULL, zodat hij lege waardes overslaat. Bij property_boolean heb ik een INDEX gemaakt met WHERE property_boolean IS TRUE, omdat ik alleen de active waardes wil weten. Default is namelijk FALSE. In de tabel contentproperty heeft elke kolom z'n eigen datatype, bijvoorbeeld property_numeric, property_date, property_text, etc. Bij het opslaan van een waarde zijn de overige kolommen dus NULL of FALSE. Zou ik dit misschien ook anders moeten aanmaken?

Na enkele ANALYZE en testen merk ik dat sommige queries een paar seconden sneller zijn.
Ik ga verder met explain om te kijken waar het nog beter kan. Ik begrijp dat ik die hash join het beste kan voorkomen.

Die complexe queries weet ik ongeveer nu te optimaliseren. Eerst wil ik die crosstabs views (de basis) optimaal hebben. Ik ben erg blij met jullie tips

[leuk_he]

Verwijderd schreef op maandag 12 januari 2009 @ 09:55:
Op de content_id en page_id zit een UNIQUE INDEX. Dit is toch voldoende?

op contentproperty.content_id ?

[Verwijderd]

Ja, contentproperty.content_id heeft een INDEX en een UNIQUE INDEX

[pasz]

MAX en COUNT zijn killers op een zeer grote tabel in een PostgreSQL database.

Google er maar eens op.

[justmental]

Verwijderd schreef op zondag 11 januari 2009 @ 13:27:
@cariolive23 dit is het resultaat van de planner:
-> Hash Join (cost=130.39..436.13 rows=3 width=157)

Hash Cond: ("outer".content_id = "inner".content_id)

-> Bitmap Heap Scan on content c (cost=6.63..311.46 rows=177 width=85)

Recheck Cond: (contenttemplate_id = '7dd361f0b71125b74f80c39d49fa9404'::bpchar)

Filter: (content_delete IS FALSE)

-> Bitmap Index Scan on templatecontent_fk (cost=0.00..6.63 rows=181 width=0)

Index Cond: (contenttemplate_id = '7dd361f0b71125b74f80c39d49fa9404'::bpchar)
[/code]

@justmental: ik filter dus page_id, zie regel 32. Of moet de WHERE page_id binnen de view definitie vallen? Zal dat meer snelheid opleveren?

Zo te zien filtert hij wel goed op het page_id.
Ik zou checken of de aantallen die de explain geeft wel kloppen. Heeft een page 151 rows in pagehascontent?
En heeft een template 181 rows in content?
Daarop baseert hij de hash join. Als een van deze getallen in werkelijkheid lager is dan is een nested loops waarschijnlijk sneller en ook minder afhankelijk van de grootte van de tabel.
Ik weet trouwens niet of je het plan kunt beinvloeden in postgres, als dat kan dan kun je testen of die nested loops beter performt.

Als postgres multi column indexen ondersteunt zou je een gecombineerde index op contenttemplate_id en content_id kunnen proberen.

[ Voor 4% gewijzigd door justmental op 12-01-2009 16:21 ]