Ergebnis 1 bis 10 von 10

Thema: Schlechte Query-Performance

  1. #1
    alger ist offline Neuer Benutzer
    Registriert seit
    14.12.2009
    Beiträge
    22

    Standard Schlechte Query-Performance

    Hallo,

    ich habe eine Abfrage geschrieben, die leider nicht optimal von PG ausgeführt wird, ich suche nach Möglichkeit den Ausführungsplan für diese Abfrage manuel zu beenflussen bzw. die Abfrage so zu gestalten dass der Ausführungsplan optimal von PG gewählt wird. Die Abfrage wird in einem View weiter verwendet.

    Also, möchte ich folgendes haben:

    Create view data_view as
    select id, avg(value) over w from data window w as (partition by value);

    Dann möchte ich die Daten weiter selektieren können, z.B.:

    select * from data_view where id=5383

    Leider rechnet PG avg(value) für alle Werte der tabelle data aus statt nur
    Records zu nehmen wo id=1 ist.

    Ich habe hier zwei Beispielabfragen mit zugehörigen QueryPlans:

    1.

    select id, avg(value) over w from data
    where id=5383
    window w as (partition by value)

    QueryPlan:

    "WindowAgg (cost=8.28..8.30 rows=1 width=12) (actual time=0.146..0.152 rows=1 loops=1)"
    " -> Sort (cost=8.28..8.29 rows=1 width=12) (actual time=0.088..0.094 rows=1 loops=1)"
    " Sort Key: value"
    " Sort Method: quicksort Memory: 17kB"
    " -> Index Scan using data_pkey on data (cost=0.00..8.27 rows=1 width=12) (actual time=0.037..0.044 rows=1 loops=1)"
    " Index Cond: (id = 5383)"
    "Total runtime: 0.322 ms"


    2.

    select * from
    (
    select id, avg(value) over w from data window w as (partition by value)
    ) as d
    where id=5383

    QueryPlan:

    "Subquery Scan d (cost=3703.44..4691.25 rows=165 width=16) (actual time=368.344..761.422 rows=1 loops=1)"
    " Filter: (d.id = 5383)"
    " -> WindowAgg (cost=3703.44..4279.67 rows=32927 width=12) (actual time=326.076..653.021 rows=32927 loops=1)"
    " -> Sort (cost=3703.44..3785.76 rows=32927 width=12) (actual time=326.036..444.920 rows=32927 loops=1)"
    " Sort Key: data.value"
    " Sort Method: external merge Disk: 704kB"
    " -> Seq Scan on data (cost=0.00..669.27 rows=32927 width=12) (actual time=0.031..114.356 rows=32927 loops=1)"
    "Total runtime: 762.056 ms"

    Also die Abfragen sind von der Logik her identisch, die Query Plans aber total unterschiedlich. Wie kann also dem PG beibringen, dass die gleich sind?

    ich bedanke mich für jede Hilfe

    Gruß

    Alger

  2. #2
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.465

    Standard

    Zitat Zitat von alger Beitrag anzeigen

    Create view data_view as
    select id, avg(value) over w from data window w as (partition by value);
    Ich grüble grad, was das für einen Sinn ergibt. Wie ist die Tabelle definiert? Ist id ein Primary key? Sieht so aus, zumindest laut erstem Plan.

    Kurzer Test:
    Code:
    test=*# select * from data;
     id | value
    ----+-------
      1 |     2
      1 |     4
      2 |     5
      3 |     6
      3 |     8
    (5 rows)
    
    test=*# select id, avg(value) over w from data window w as (partition by data) order by 1;
     id | avg
    ----+-----
      1 |   2
      1 |   4
      2 |   5
      3 |   6
      3 |   8
    (5 rows)
    Also, irgendwie übersehe ich grad etwas, aber ich erkenne keinen Sinn in der Anwendung der window_Funktion.


    Dann möchte ich die Daten weiter selektieren können, z.B.:

    select * from data_view where id=5383

    Leider rechnet PG avg(value) für alle Werte der tabelle data aus statt nur
    Records zu nehmen wo id=1 ist.
    Ja, der 2. Plan sieht Scheiße aus, aber das kommt meiner Meinung nach durch die Windowing-function.

    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  3. #3
    alger ist offline Neuer Benutzer
    Registriert seit
    14.12.2009
    Beiträge
    22

    Standard

    Hallo Andreas, ja du hast recht, der zweite Plan ist wegen der window function so schlecht. Aber gerade hier ist ja mein Problem, ich möchte die window fnkt. in der abfrage behalten und trotzdem gute performance haben. Der Aufruf der avg-fnkt. in der Abfrage ist beispielhaft, ich werde an der Stelle eine andere selbstgeschriebene agreg. fnkt. einsetzen, die ich als window fnkt. verwende. Diese funktion ist sehr rechenintensiv, damit sie für die gleichen argumente nur einmal pro Abfrage gerechnet wird habe ich sie als window-fnkt. implementiert, d.h. wenn eine Abfrage wie select value, meine_fntk(value) from data 10 ergebnisse liefert, wird meine_fntk(value) 10 mal gerechnet ganz egal ob values gleich oder unterschiedlcih sind. falls ich aber meine_fntk als aggreg. fnkt. schreibe und dann folgende abfrage absetze: select value, meine_fntk(value) over w from data window w as (partition by value) wird sie nur einmal/distinct value gerechnet. Damit habe ich deutlche performance steigerung, aber ich muss das ganze in eine Sicht einbinden und das klappt wegen schlechter Ausführungsplanwahl nicht.

  4. #4
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.465

    Standard

    Zitat Zitat von alger Beitrag anzeigen
    Hallo Andreas, ja du hast recht, der zweite Plan ist wegen der window function so schlecht. Aber gerade hier ist ja mein Problem, ich möchte die window fnkt. in der abfrage behalten und trotzdem gute performance haben.
    Ich kann das gut verstehen, mir hat das auch jetzt keine Ruhe gelassen und ich hab damit rumgespielt und auch dies in einer der Mailinglisten nachgefragt. Geantwortet hat mir Tom Lane, wer die Szene kennt, weiß, wer das ist ...

    Also:

    das Problem ist, daß durch das rausziehen der WHERE-Condition und der Windowing-Funktion als Subquery sich das Resultat derselben ändern kann, es entsteht sehr leicht eine komplett andere Abfrage.

    Beispiel:

    Code:
    test=# select * from values;
     id | value
    ----+-------
      1 |    10
      2 |    20
      3 |    30
      4 |    40
      5 |    50
      6 |    60
      7 |    70
      8 |    80
      9 |    90
    (9 rows)
    
    Time: 0.240 ms
    test=*# select id, sum(value) over (order by id) from values where id = 5;
     id | sum
    ----+-----
      5 |  50
    (1 row)
    
    Time: 0.352 ms
    test=*# select * from (select id, sum(value) over (order by id) from values) foo where id = 5;
     id | sum
    ----+-----
      5 | 150
    (1 row)
    Daher ist klar, daß der Plan anders sein muß. In Deinem Beispiel ist es eher Zufall, daß beide Resultate gleich sind (ich hab damit probiert, mit einer ähnlichen Tabelle und sehr vielen Records).

    Also, wenn Du einen Rechenweg oder eben die Umstände definieren kannst, wo das Resultat gleich ist, dann ab an Tom Lane damit ...


    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  5. #5
    alger ist offline Neuer Benutzer
    Registriert seit
    14.12.2009
    Beiträge
    22

    Standard

    Hi Andreas, jetzt habe die Problematik bei der Verwendung von Window Funktionen verstanden, aber die tritt nur dann auf wenn die ORDER BY-klausel verwendet wird. Falls man aber keine Sortierung angibt und nur die PARTITION BY-klausel definiert ist, müssen doch beide Ergebnisse gleich sein? Ich habe dein Beispiel nachgebildet:

    Code:
    select id, sum(value) over (partition by id) from values where id = 5;
    
    id | sum
    ----+-----
      5 |  50
    
    select * from (select id, sum(value) over (partition by id) from values) foo where id = 5;
    
    id | sum
    ----+-----
      5 |  50
    Herrn Tom Lane kenne ich eigentlcih nur vom Namen her, könntest du mir
    seine email mitteilen oder wie ich sonst mit ihm Kontakt aufnehmen kann?

    Noch eine Frage hätte ich: die gesamte Problematik ist ja nur dadurch entstanden, dass die Ergebnisse einer Funktion innerhalb einer Abfrage von PG nicht zwischen gespeichert werden, obwohl die Funktion als STRICT und STABLE gekennzeichnet ist. Gibt es eine Möglichkeit dem PG zu mitteilen, dass es die Funktionergebnisse zwischengespeichert werden sollen?
    Also, die folgenden Abfragen sollen etwa die gleiche Laufzeit haben:
    Code:
    select * from values;
     id | value
    ----+-------
      1 |    10
      2 |    10
      3 |    10
      4 |    10
      5 |    10
      6 |    20
      7 |    20
      8 |    20
      9 |    20
    
    1) select value, rechenintensive_fnkt(value) from values where value=10 and id=1;
    
    2) select value, rechenintensive_fnkt(value) from values where value=10 and id between 1 and 5;
    Danke

    gruß

    Alger

  6. #6
    castorp ist offline Erfahrener Benutzer
    Registriert seit
    27.06.2009
    Beiträge
    524

    Standard

    Zitat Zitat von alger Beitrag anzeigen
    Gibt es eine Möglichkeit dem PG zu mitteilen, dass es die Funktionergebnisse zwischengespeichert werden sollen?
    Nachdem Du ja weisst für welchen Wert das passieren soll, müsste doch das folgende funktionieren:
    Code:
    WITH ergebnis (wert) AS
    (
      SELECT rechenintensive_fnkt(10)
    )
    SELECT v.id, e.wert
    FROM values v
      CROSS JOIN ergebnis e ON e.id = v.id
    WHERE v.id BETWEEN 1 AND 5;
    Aber vielleicht habe ich auch die Aufgabe falsch verstanden

  7. #7
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.465

    Standard

    Zitat Zitat von alger Beitrag anzeigen
    Hi Andreas, jetzt habe die Problematik bei der Verwendung von Window Funktionen verstanden, aber die tritt nur dann auf wenn die ORDER BY-klausel verwendet wird. Falls man aber keine Sortierung angibt und nur die PARTITION BY-klausel definiert ist, müssen doch beide Ergebnisse gleich sein? Ich habe dein Beispiel nachgebildet:

    Code:
    select id, sum(value) over (partition by id) from values where id = 5;
    
    id | sum
    ----+-----
      5 |  50
    
    select * from (select id, sum(value) over (partition by id) from values) foo where id = 5;
    
    id | sum
    ----+-----
      5 |  50
    Stellst sich weiterhin die Frage nach dem tieferen Sinn dieser Abfragen...


    Herrn Tom Lane kenne ich eigentlcih nur vom Namen her, könntest du mir
    seine email mitteilen oder wie ich sonst mit ihm Kontakt aufnehmen kann?
    Wenn Du das vorhast, solltest Du dies über die normale Mailingliste tun. Ich tat auch nur das. Es war diese Liste:

    PostgreSQL Mailing Lists: pgsql-performance

    Und dort dieser Thread: bad execution plan for subselects containing windowing-function

    Noch eine Frage hätte ich: die gesamte Problematik ist ja nur dadurch entstanden, dass die Ergebnisse einer Funktion innerhalb einer Abfrage von PG nicht zwischen gespeichert werden
    Nein, das Problem ist eher, daß die Abfragen komplett unterschiedliche Dinge tun, aufgrund der CTE drin.

    , obwohl die Funktion als STRICT und STABLE gekennzeichnet ist. Gibt es eine Möglichkeit dem PG zu mitteilen, dass es die Funktionergebnisse zwischengespeichert werden sollen?
    Du könntest Dir das mal anschauen: PostgreSQL/Materialized Views - Jonathan Gardner's Tech Wiki

    Also, die folgenden Abfragen sollen etwa die gleiche Laufzeit haben:
    Code:
    select * from values;
     id | value
    ----+-------
      1 |    10
      2 |    10
      3 |    10
      4 |    10
      5 |    10
      6 |    20
      7 |    20
      8 |    20
      9 |    20
    
    1) select value, rechenintensive_fnkt(value) from values where value=10 and id=1;
    
    2) select value, rechenintensive_fnkt(value) from values where value=10 and id between 1 and 5;
    Danke

    [/quote]
    Ja, das hat aber nix mit CTE zu tun. Wenn Du Dein Problem auf so eine Lösung reduzieren kannst, dann mache das und fertig ist.


    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  8. #8
    alger ist offline Neuer Benutzer
    Registriert seit
    14.12.2009
    Beiträge
    22

    Standard

    Zitat Zitat von castorp Beitrag anzeigen
    Nachdem Du ja weisst für welchen Wert das passieren soll, müsste doch das folgende funktionieren:
    Code:
    WITH ergebnis (wert) AS
    (
      SELECT rechenintensive_fnkt(10)
    )
    SELECT v.id, e.wert
    FROM values v
      CROSS JOIN ergebnis e ON e.id = v.id
    WHERE v.id BETWEEN 1 AND 5;
    Aber vielleicht habe ich auch die Aufgabe falsch verstanden
    Das ist ja gerade das Problem, ich kenne die fnkt.-Argumente vorher nicht. Das Ziel ist ja dem Benutzer eine Sicht zur Verfügung zu stellen
    Code:
    create view view_values as select id, rechenintensiv_fnkt(value) over (partition by value) from values;
    so der Benuter Abfragen wie
    Code:
    select * from view_values where id=50;
    perfomant ausführen könnte und nicht darauf wartete bis PG das Ergebniss für die komplette Tabelle ausrechnet und erst dann Zeile mit der entsprechenden ID selektiert.

  9. #9
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.465

    Standard

    Zitat Zitat von alger Beitrag anzeigen
    Das ist ja gerade das Problem, ich kenne die fnkt.-Argumente vorher nicht. Das Ziel ist ja dem Benutzer eine Sicht zur Verfügung zu stellen
    Code:
    create view view_values as select id, rechenintensiv_fnkt(value) over (partition by value) from values;
    Schreib es um.

    Code:
    test=*# \d werte
         Table "public.werte"
     Column |  Type   | Modifiers
    --------+---------+-----------
     id     | integer |
     wert   | real    |
    Indexes:
        "idx_id" btree (id)
        "idx_wert" btree (wert)
    
    test=*# \d bla
               View "public.bla"
     Column |       Type       | Modifiers
    --------+------------------+-----------
     wert   | real             |
     avg    | double precision |
    View definition:
     SELECT werte.wert, avg(werte.wert) AS avg
       FROM werte
      GROUP BY werte.wert;
    
    test=*# explain analyse select v.id, b.avg from werte v right join bla b on (v.wert=b.wert) where v.id = 5383;
                                                                    QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------
     Merge Join  (cost=8.29..7164.31 rows=498 width=12) (actual time=448.912..448.922 rows=1 loops=1)
       Merge Cond: (werte.wert = v.wert)
       ->  GroupAggregate  (cost=0.00..5901.78 rows=99642 width=4) (actual time=0.054..390.199 rows=94210 loops=1)
             ->  Index Scan using idx_wert on werte  (cost=0.00..4156.26 rows=100000 width=4) (actual time=0.033..195.019 rows=94391 loops=1)
       ->  Sort  (cost=8.29..8.29 rows=1 width=8) (actual time=0.036..0.037 rows=1 loops=1)
             Sort Key: v.wert
             Sort Method:  quicksort  Memory: 17kB
             ->  Index Scan using idx_id on werte v  (cost=0.00..8.28 rows=1 width=8) (actual time=0.017..0.020 rows=1 loops=1)
                   Index Cond: (id = 5383)
     Total runtime: 449.062 ms
    (10 rows)
    In werte sind 100.000 Datensätze, fast alle unique. Mein VIEW enthält je wert in werte den avg(wert). Das ist zwar logischer Durchfall, aber egal. Das joine ich gegen die Ursprungstabelle, über die wert - Spalte. Wie im Explain zu sehen ist, nutzt es die vorhandenen Indexe.

    Nochmals: ein avg(wert) over (partition by wert) ist unsinnig. Oder ich bin grad zu blöd, den Sinn zu erkennen, was durchaus nicht auszuschließen ist.


    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  10. #10
    alger ist offline Neuer Benutzer
    Registriert seit
    14.12.2009
    Beiträge
    22

    Standard

    Hi Andreas,

    also ich versuche jetzt das Problem ausführlicher zu erklären. Ich habe zuerst versucht es an einigen Beispielen zu schildern, aber vermutlich ist es besser wenn ich das ganze komplett beschreibe.

    Ich habe eine Tabelle in der fortlaufend Messdaten gespeichert werden. Mit einer Taktfrequenz von etwa 5-10 Hz werden die Daten in die Tabelle geschrieben.
    Code:
    CREATE TABLE data
    (
      id bigserial NOT NULL,
      create_date timestamp without time zone DEFAULT now(),
      error integer NOT NULL,
      value real NOT NULL,
      property_id integer,       -- Merkmal
      productionlot_id integer, -- Fertigungslos
      master_id bigint,           -- Gruppe_ID
      CONSTRAINT data_pkey PRIMARY KEY (id)
    )
    Die Daten werden gruppenweise gemessen, stell dir z.b. vor dass eine Fläche an 30-50 unterschiedlichen Punkten vermessen wird, die dann alle logisch zur gleichen Gruppe (master_id) gehören.

    Jetzt möchte man dem Benuter einen View zur Verfügung stellen, so dass er alle Daten der Tabelle data sowie die Ergebnisse eines Grubbs-Tests (s. http://www.pg-forum.de/sql/4074-ausr...ostgresql.html) hat, wobei er die Daten über die Selektion von property_id, master_id oder productionId dem View etnehmen könnte:
    Code:
    create or replace view data_view as select data.*,case when id = any (grubbs_testing(master_id)) then true else false end as is_outlier from data;
    select * from data_view where master_id=5383;
    
    Query-Plan:
    Seq Scan on data  (cost=0.00..2251.59 rows=6 width=51) (actual time=136.647..572.636 rows=10 loops=1)
    Filter: (master_id = 5383)
    Total runtime: 572.764 ms
    
    select grubbs_testing(master_id) from (select distinct master_id from data where master_id=5383) d;
    
    Query-Plan:
    Subquery Scan d  (cost=0.00..1001.61 rows=1 width=8) (actual time=133.460..143.296 rows=1 loops=1)
      ->  Unique  (cost=0.00..751.60 rows=1 width=8) (actual time=4.018..13.844 rows=1 loops=1)"
            ->  Seq Scan on data  (cost=0.00..751.59 rows=6 width=8) (actual time=4.002..13.780 rows=10 loops=1)
                 Filter: (master_id = 5383)"
    Total runtime: 143.380 ms"
    Also wird die Laufzeit der Abfrage im View größtenteils durch die Laufzeit der Aufrufe der Funktion grubbs_testing bestimmt, die wird glaube ich für jeden Datensatz der Ergebnismenge aufgerufen.

    Also habe ich mich entschieden, eine custom aggregate funktion zu schreiben, die als final function meine grubbs_testing aufruft, und diese aggreg.fnkt. als window function einzusetzen. So kann ich erzwingen, dass sie nur einmal pro master_id aufgerufen wird:

    Code:
    CREATE AGGREGATE outliers(bigint) (
      SFUNC=last_element,
      STYPE=int8,
      FINALFUNC=grubbs_testing
    );
    
    select data.*,case when id = any (outliers(master_id) over w) then true else false end as is_outlier from data where master_id=5383
    window w as (partition by master_id) ;
    
    Query-Plan:
    
    "WindowAgg  (cost=0.00..751.74 rows=6 width=51) (actual time=143.805..143.839 rows=10 loops=1)"
    "  ->  Seq Scan on data  (cost=0.00..751.59 rows=6 width=51) (actual time=3.965..24.097 rows=10 loops=1)"
    "        Filter: (master_id = 5383)"
    "Total runtime: 144.005 ms"
    Wie man sieht ist dieser Aufruf 4-mal schneller als der im View da oben.
    Das Problem ist aber wenn ich diese Abfrage in die View-Definition reinpacke (ohne WHERE) dann habe ich der Fall wie am Anfang des Thread beschrieben: Zuerst wird die grubbs_testing() für alle Daten berechnet und erst anschließend die Datensätze die man braucht selektiert.

    Ich hoffe, dass ich jetzt alles besser erklärte.

    Übrigens habe ich jetzt die Abfragen mit Window AS (Partition by foo) und ohne Order by getestet und kein Fall gefunden wo die Ergebnisse von einem Subselect sich von eines Select unterscheiden (abgesehen von der Laufzeit/Query Plan).

Ähnliche Themen

  1. Frage Performance
    Von tme2525 im Forum Allgemeines
    Antworten: 2
    Letzter Beitrag: 21.08.2010, 19:47
  2. Performance tuning
    Von acki4711 im Forum Allgemeines
    Antworten: 39
    Letzter Beitrag: 01.10.2009, 16:38
  3. Problem Performance ... COPY FROM
    Von Todidlidu im Forum Allgemeines
    Antworten: 19
    Letzter Beitrag: 28.05.2008, 12:49
  4. Antworten: 0
    Letzter Beitrag: 11.02.2008, 13:28
  5. Performance
    Von W.K. im Forum Allgemeines
    Antworten: 17
    Letzter Beitrag: 16.12.2007, 07:59

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum