+ Antworten
Seite 1 von 2 1 2 LetzteLetzte
Ergebnis 1 bis 10 von 11

Thema: Abfrage optimieren aber wie?

  1. #1
    spinner0815 ist offline Neuer Benutzer
    Registriert seit
    15.03.2010
    Beiträge
    12

    Standard Abfrage optimieren aber wie?

    Hi,

    ich bin neu bei postgresql und wollte mal fragen wie ich diese Abfrage ein wenig vereinfachen kann damit sie auch schneller ist.
    Code:
    SELECT "domainName",
                        (SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 14 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_results,
                        (SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 3 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as whois,
                        (SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 12 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_backlinks,
                        (SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 13 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_sitelinks,
                        (SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 30 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as Dchk,
                        (SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 22 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_pagerank,
                        ( SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 10 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as alexa_rank
                    FROM history as greathistory
                    WHERE "domainName" like 'w%' GROUP BY "domainName" ORDER BY "domainName";
    
    Das problem ist das die Tabelle riesig ist da kommt sozusagen alles rein. Nur ist es leider viel zu umständlich für jeden einzelnen "historyType" eine eigene Tabelle anzulegen da ich dann extrem viel code ändern müsste (eine java applikation ist da mit dran).
    Die history Tabelle ist wie folgt afugebaut:
    historyID | DomainName | historyTimestamp | historyType | historyTypeValue | historyServer

    und ich möchte halt zu der dazugehörigen Domain den aktuellen typevalue zu den richtige type haben.

    Z.b. suche ich mit
    Code:
     ORDER BY "historyTimestamp" DESC LIMIT 1
    
    das aktuellste Datum des typs raus. gibt es da vielleicht eine andere, schnellere variante?
    Ich habs mit
    Code:
    AND "historyTimestamp" = (SELECT max("historyTimestamp") FROM history)
    
    doch das ist leider langsamer als die ursprüngliche Methode.
    Desweiteren ist mir die innere SELECT anfrage ein dorn im auge, da er ja dann jedesmal wieder eine neue tabelle aufmacht
    Code:
    SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 14 ORDER BY "historyTimestamp" DESC LIMIT 1)
    
    (glaub ich zumindest). doch leider muss ich ja die verbindung herstellen damit der name mit den namen der äusseren abfrage verglichen wird.
    Oder gibt es da eine bessere methode?


    hoffe ihr könnt mir bei der optimierung helfen

    vielen dank schonmal

    spinner0815
    Geändert von spinner0815 (15.03.2010 um 11:54 Uhr) Grund: CODE] hat gefehlt

  2. #2
    akretschmer ist offline Erfahrener Benutzer
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    4.478

    Standard

    Zitat Zitat von spinner0815 Beitrag anzeigen
    Hi,

    ich bin neu bei postgresql und wollte mal fragen wie ich diese Abfrage ein wenig vereinfachen kann damit sie auch schneller ist.
    Code:
    SELECT "domainName",
                        (SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 14 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_results,
                        (SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 3 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as whois,
                        (SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 12 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_backlinks,
                        (SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 13 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_sitelinks,
                        (SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 30 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as Dchk,
                        (SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 22 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_pagerank,
                        ( SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 10 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as alexa_rank
                    FROM history as greathistory
                    WHERE "domainName" like 'w%' GROUP BY "domainName" ORDER BY "domainName";
    
    Das problem ist das die Tabelle riesig ist da kommt sozusagen alles rein. Nur ist es leider viel zu umständlich für jeden einzelnen "historyType" eine eigene Tabelle anzulegen da ich dann extrem viel code ändern müsste (eine java applikation ist da mit dran).
    Die history Tabelle ist wie folgt afugebaut:
    historyID | DomainName | historyTimestamp | historyType | historyTypeValue | historyServer

    und ich möchte halt zu der dazugehörigen Domain den aktuellen typevalue zu den richtige type haben.

    Z.b. suche ich mit
    Code:
     ORDER BY "historyTimestamp" DESC LIMIT 1
    
    das aktuellste Datum des typs raus. gibt es da vielleicht eine andere, schnellere variante?
    Ich habs mit [AND "historyTimestamp" = (SELECT max("historyTimestamp") FROM history)[/CODE] doch das ist leider langsamer als die ursprüngliche Methode.
    Desweiteren ist mir die innere SELECT anfrage ein dorn im auge, da er ja dann jedesmal wieder eine neue tabelle aufmacht
    Code:
    SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 14 ORDER BY "historyTimestamp" DESC LIMIT 1)
    
    (glaub ich zumindest). doch leider muss ich ja die verbindung herstellen damit der name mit den namen der äusseren abfrage verglichen wird.
    Oder gibt es da eine bessere methode?


    hoffe ihr könnt mir bei der optimierung helfen

    vielen dank schonmal

    spinner0815
    So aus der kalten heraus (hab grad nicht wirklich zeit) würde ich vorschlagen, deine 7 subselects als eines zu machen, etwa so:

    Code:
    SELECT "historyTypeValue" , "historyType", max("historyTimestamp") from ... where  "historyType" in (...) group by 1,2
    
    (statt 1,2 die richtigen Spaltennamen schreiben!)

    Damit wäre dies ein Tablescan und nicht 7. Dagegen kannst Du dann joinen. Das ORDER BY und LIMT 1 um min/max zu finden würde ich gegen die passenden Funktionen min() und max() tauschen, dazu sind diese da. Hast Du überhaupt passende Indexe?

    Und ja, wenn die Tabelle Dir als zu groß erscheint: table-partitioning wurde bereits erfunden.

    Was genau meint einklich EXPLAIN ANALYSE zu Deiner Abfrage, und welche Version ist am Start?
    --
    Papa-Kind-Kur vom 8. bis 29. September ;-)
    PostgreSQL Hosting
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    http://www.amazon.de/gp/registry/2APKFICAIYMWB
    https://www.xing.com/profile/Andreas_Kretschmer7

  3. #3
    spinner0815 ist offline Neuer Benutzer
    Registriert seit
    15.03.2010
    Beiträge
    12

    Standard

    ich hätte wohl erstmal schreiben sollen wie mein ergebnis aussehen soll
    Also es soll dann eine Tabelle der form:
    domainName | google_results | whois | google_backlinks | google_sitelinks | dchk | google_pagerank | alexa_rank

    rauskommen. So das nach domainName sortiert wird. Wenn ich die subselects zu einer zusammenfüge gehts ja nich da ich ja nur eine eine Zeile pro subselect zurückgeben kann. und ich wollte halt zu jedem historyTypevalue eine eigene spalte haben.

    und die abfrage mit dem min()/max() da dauert länger als
    Code:
    ORDER BY "historyTimestamp" DESC LIMIT 1
    
    Indexe hab ich bereits drin

    EXPLAIN:

    "Sort (cost=232402.51..232412.33 rows=3926 width=17)"
    " Sort Key: greathistory."domainName""
    " -> HashAggregate (cost=3603.97..232168.15 rows=3926 width=17)"
    " -> Seq Scan on history greathistory (cost=0.00..3577.76 rows=10484 width=17)"
    " Filter: (("domainName")::text ~~ 'w%'::text)"
    " SubPlan 1"
    " -> Limit (cost=0.00..8.32 rows=1 width=16)"
    " -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16)"
    " Index Cond: (("historyType" = 14) AND (("domainName")::text = ($0)::text))"
    " SubPlan 2"
    " -> Limit (cost=0.00..8.32 rows=1 width=16)"
    " -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16)"
    " Index Cond: (("historyType" = 3) AND (("domainName")::text = ($0)::text))"
    " SubPlan 3"
    " -> Limit (cost=0.00..8.32 rows=1 width=16)"
    " -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16)"
    " Index Cond: (("historyType" = 12) AND (("domainName")::text = ($0)::text))"
    " SubPlan 4"
    " -> Limit (cost=0.00..8.32 rows=1 width=16)"
    " -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16)"
    " Index Cond: (("historyType" = 13) AND (("domainName")::text = ($0)::text))"
    " SubPlan 5"
    " -> Limit (cost=0.00..8.32 rows=1 width=16)"
    " -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16)"
    " Index Cond: (("historyType" = 30) AND (("domainName")::text = ($0)::text))"
    " SubPlan 6"
    " -> Limit (cost=0.00..8.32 rows=1 width=16)"
    " -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16)"
    " Index Cond: (("historyType" = 22) AND (("domainName")::text = ($0)::text))"
    " SubPlan 7"
    " -> Limit (cost=0.00..8.32 rows=1 width=16)"
    " -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16)"
    " Index Cond: (("historyType" = 10) AND (("domainName")::text = ($0)::text))"
    udn die sache mit dem Table-Partitioning werd ich mir mal genauer anschaun glaub ich
    Ich hab das mal gegooglet und da ist PostgreSQL Maestro rausgekommen. ist das so ein tool dafür oder geht das auch per abfrage?

    edit: ok PostgreSQL Maestro scheint wohl ein weiteres admin tool zu sein.
    Geändert von spinner0815 (15.03.2010 um 12:28 Uhr) Grund: PostgreSQL Maestro

  4. #4
    akretschmer ist offline Erfahrener Benutzer
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    4.478

    Standard

    Zitat Zitat von spinner0815 Beitrag anzeigen
    ich hätte wohl erstmal schreiben sollen wie mein ergebnis aussehen soll
    Also es soll dann eine Tabelle der form:
    domainName | google_results | whois | google_backlinks | google_sitelinks | dchk | google_pagerank | alexa_rank
    http://www.pg-forum.de/h-ufig-gestel...u-spalten.html

    Indexe hab ich bereits drin
    Code:
    " -> Seq Scan on history greathistory (cost=0.00..3577.76 rows=10484 width=17)"
    " Filter: (("domainName")::text ~~ 'w%'::text)"
    
    Nun ja:

    Code:
    test=# EXPLAIN ANALYZE SELECT * FROM words where t like 'w%';
                                                   QUERY PLAN
    ---------------------------------------------------------------------------------------------------------
     Seq Scan on words  (cost=0.00..1729.11 rows=996 width=9) (actual time=47.895..51.696 rows=2209 loops=1)
       Filter: (t ~~ 'w%'::text)
     Total runtime: 54.309 ms
    (3 rows)
    
    test=# create index idx_words on words(t);
    CREATE INDEX
    test=# EXPLAIN ANALYZE SELECT * FROM words where t like 'w%';
                                                   QUERY PLAN
    ---------------------------------------------------------------------------------------------------------
     Seq Scan on words  (cost=0.00..1729.11 rows=996 width=9) (actual time=27.537..30.705 rows=2209 loops=1)
       Filter: (t ~~ 'w%'::text)
     Total runtime: 33.115 ms
    (3 rows)
    
    test=# drop index idx_words ;
    DROP INDEX
    test=# create index idx_words on words(t text_pattern_ops);
    CREATE INDEX
    test=# EXPLAIN ANALYZE SELECT * FROM words where t like 'w%';
                                                          QUERY PLAN
    ----------------------------------------------------------------------------------------------------------------------
     Index Scan using idx_words on words  (cost=0.00..8.28 rows=996 width=9) (actual time=0.322..5.796 rows=2209 loops=1)
       Index Cond: ((t ~>=~ 'w'::text) AND (t ~<~ 'x'::text))
       Filter: (t ~~ 'w%'::text)
     Total runtime: 8.450 ms
    (4 rows)
    
    Man muß halt auch schauen, ob diese zur Abfrage passen.


    udn die sache mit dem Table-Partitioning werd ich mir mal genauer anschaun glaub ich
    Jo, tue das.


    Warum einklich das GROUP BY in der Abfrage? Funktioniert das überhaupt, ohne Aggregationen in den anderen Spalten?


    Andreas
    --
    Papa-Kind-Kur vom 8. bis 29. September ;-)
    PostgreSQL Hosting
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    http://www.amazon.de/gp/registry/2APKFICAIYMWB
    https://www.xing.com/profile/Andreas_Kretschmer7

  5. #5
    spinner0815 ist offline Neuer Benutzer
    Registriert seit
    15.03.2010
    Beiträge
    12

    Standard

    so ich hab jetzt mal die analyse funktion von pgAdmin genommen:
    Sort (cost=226042.94..226052.48 rows=3817 width=17) (actual time=274.194..274.345 rows=4088 loops=1)
    Output: greathistory."domainName", ((SubPlan 1)), ((SubPlan 2)), ((SubPlan 3)), ((SubPlan 4)), ((SubPlan 5)), ((SubPlan 6)), ((SubPlan 7))
    Sort Key: greathistory."domainName""
    Sort Method: quicksort Memory: 330kB"
    -> HashAggregate (cost=3588.90..225815.86 rows=3817 width=17) (actual time=34.630..251.572 rows=4088 loops=1)
    Output: greathistory."domainName", (SubPlan 1), (SubPlan 2), (SubPlan 3), (SubPlan 4), (SubPlan 5), (SubPlan 6), (SubPlan 7)
    -> Seq Scan on history greathistory (cost=0.00..3562.81 rows=10435 width=17) (actual time=14.664..31.224 rows=10751 loops=1)
    Output: greathistory."historyID", greathistory."domainName", greathistory."historyTimestamp", greathistory."historyType", greathistory."historyTypeValue", greathistory."historyServer"
    Filter: (("domainName")::text ~~ 'w%'::text)
    SubPlan 1
    -> Limit (cost=0.00..8.32 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=4088)
    Output: public.history."historyTypeValue"
    -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=4088)
    Output: public.history."historyTypeValue"
    Index Cond: (("historyType" = 14) AND (("domainName")::text = ($0)::text))
    SubPlan 2
    -> Limit (cost=0.00..8.32 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=4088)
    Output: public.history."historyTypeValue"
    -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16) (actual time=0.010..0.010 rows=0 loops=4088)
    Output: public.history."historyTypeValue"
    Index Cond: (("historyType" = 3) AND (("domainName")::text = ($0)::text))
    SubPlan 3
    -> Limit (cost=0.00..8.32 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=4088)
    Output: public.history."historyTypeValue"
    -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4088)
    Output: public.history."historyTypeValue"
    Index Cond: (("historyType" = 12) AND (("domainName")::text = ($0)::text))
    SubPlan 4
    -> Limit (cost=0.00..8.32 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=4088)
    Output: public.history."historyTypeValue"
    -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4088)
    Output: public.history."historyTypeValue"
    Index Cond: (("historyType" = 13) AND (("domainName")::text = ($0)::text))
    SubPlan 5
    -> Limit (cost=0.00..8.32 rows=1 width=16) (actual time=0.010..0.010 rows=0 loops=4088)
    Output: public.history."historyTypeValue"
    -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=4088)
    Output: public.history."historyTypeValue"
    Index Cond: (("historyType" = 30) AND (("domainName")::text = ($0)::text))
    SubPlan 6
    -> Limit (cost=0.00..8.32 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=4088)
    Output: public.history."historyTypeValue"
    -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=4088)
    Output: public.history."historyTypeValue"
    Index Cond: (("historyType" = 22) AND (("domainName")::text = ($0)::text))"
    SubPlan 7
    -> Limit (cost=0.00..8.32 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=4088)"
    Output: public.history."historyTypeValue"
    -> Index Scan Backward using history_history_idx on history (cost=0.00..8.32 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=4088)
    Output: public.history."historyTypeValue"
    Index Cond: (("historyType" = 10) AND (("domainName")::text = ($0)::text))
    Total runtime: 274.817 ms
    hier die dazugehörige Abfrage:
    Code:
    SELECT "domainName",
                        (SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 14 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_results,
                        (SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 3 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as whois,
                        (SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 12 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_backlinks,
                        (SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 13 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_sitelinks,
                        (SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 30 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as Dchk,
                        (SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 22 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_pagerank,
                        ( SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 10 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as alexa_rank
                    FROM history as greathistory
                    WHERE "domainName" like 'w%' GROUP BY "domainName" ORDER BY "domainName";
    
    sieht in meinen augen völlig ok aus.

    desweiteren habe ich das mit dem max() nochmal probiert (in subselect 1):
    Code:
    SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 14 group by "historyTypeValue" ORDER BY max("historyTimestamp") DESC LIMIT 1
    
    leider ist die version langsamer als die ursprüngliche. und ich muss (warum eigentlich?) ein group by "historyTypeValue" mit reinmachen.
    dann hab ich es so probiert:
    Code:
    SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 14 and "historyTimestamp"=(SELECT max("historyTimestamp") FROM history))
    
    nur leider ist das auch nicht viel schneller

    das group by muss ich machen da sonst mehrmals der selbe Domain name erscheint. und genau das wollte ich ja vermeiden.
    ich hab mir auch deinen link zum faq angeshcaut doch leider ist da nix brauchbares dabei und leider komm ich auch nciht drumrum um die version mit den subselects oder? die tabelle wird ja richtig ausgegeben. so will ich sie ja haben...bloß schneller

  6. #6
    akretschmer ist offline Erfahrener Benutzer
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    4.478

    Standard

    Zitat Zitat von spinner0815 Beitrag anzeigen
    so ich hab jetzt mal die analyse funktion von pgAdmin genommen:

    hier die dazugehörige Abfrage:
    Code:
    SELECT "domainName",
                        (SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 14 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_results,
                        (SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 3 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as whois,
                        (SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 12 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_backlinks,
                        (SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 13 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_sitelinks,
                        (SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 30 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as Dchk,
                        (SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 22 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as google_pagerank,
                        ( SELECT "historyTypeValue"  FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 10 ORDER BY "historyTimestamp" DESC LIMIT 1)
                        as alexa_rank
                    FROM history as greathistory
                    WHERE "domainName" like 'w%' GROUP BY "domainName" ORDER BY "domainName";
    
    sieht in meinen augen völlig ok aus.

    desweiteren habe ich das mit dem max() nochmal probiert (in subselect 1):
    Code:
    SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 14 group by "historyTypeValue" ORDER BY max("historyTimestamp") DESC LIMIT 1
    
    leider ist die version langsamer als die ursprüngliche. und ich muss (warum eigentlich?) ein group by "historyTypeValue" mit reinmachen.
    dann hab ich es so probiert:
    Code:
    SELECT "historyTypeValue" FROM history
                            WHERE "domainName" = greathistory."domainName" and "historyType" = 14 and "historyTimestamp"=(SELECT max("historyTimestamp") FROM history))
    
    nur leider ist das auch nicht viel schneller

    das group by muss ich machen da sonst mehrmals der selbe Domain name erscheint. und genau das wollte ich ja vermeiden.
    ich hab mir auch deinen link zum faq angeshcaut doch leider ist da nix brauchbares dabei und leider komm ich auch nciht drumrum um die version mit den subselects oder? die tabelle wird ja richtig ausgegeben. so will ich sie ja haben...bloß schneller
    Die teuersten Zeilen sind:

    Code:
    -> HashAggregate (cost=3588.90..225815.86 rows=3817 width=17) (actual time=34.630..251.572 rows=4088 loops=1)
    Output: greathistory."domainName", (SubPlan 1), (SubPlan 2), (SubPlan 3), (SubPlan 4), (SubPlan 5), (SubPlan 6), (SubPlan 7)
    -> Seq Scan on history greathistory (cost=0.00..3562.81 rows=10435 width=17) (actual time=14.664..31.224 rows=10751 loops=1)
    Output: greathistory."historyID", greathistory."domainName", greathistory."historyTimestamp", greathistory."historyType", greathistory."historyTypeValue", greathistory."historyServer"
    Filter: (("domainName")::text ~~ 'w%'::text)
    
    Der Seq-Scan könnte durch einen passenden Index erschlagen werden, wie, zeigte ich schon. Das Hauptproblem ist aber der HashAggregate, der durch Dein GROUP BY kommt.

    Du kannst mal versuchen, die Abfrage so umzustellen:

    Code:
    select history.DomainName, foo.google_results 
    from history left join (
      select distinct on (historyTimestamp) 
        domainName, 
        historyType, 
        case when historyType=14 then historyTypeValue else null end as google_results 
      from history where historyType in (3,10,12,13,14,22,30) 
      and domainName like 'w%'
      order by historyTimestamp
    ) foo on history.DomainName=foo.DomainName;
    
    Ich habe Deine Tabelle so genommen, nur auf das Quoten der Spaltenbezeichner wegens Faulheit verzichtet, außerdem hab ich das nur für Typ 14 gemacht.

    Ob das schneller geht, kann ich aber nicht garantieren. Rückinfo von Dir wäre aber nett ...

    Andreas
    --
    Papa-Kind-Kur vom 8. bis 29. September ;-)
    PostgreSQL Hosting
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    http://www.amazon.de/gp/registry/2APKFICAIYMWB
    https://www.xing.com/profile/Andreas_Kretschmer7

  7. #7
    akretschmer ist offline Erfahrener Benutzer
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    4.478

    Standard

    Zitat Zitat von akretschmer Beitrag anzeigen
    Die teuersten Zeilen sind:

    Code:
    -> HashAggregate (cost=3588.90..225815.86 rows=3817 width=17) (actual time=34.630..251.572 rows=4088 loops=1)
    Output: greathistory."domainName", (SubPlan 1), (SubPlan 2), (SubPlan 3), (SubPlan 4), (SubPlan 5), (SubPlan 6), (SubPlan 7)
    -> Seq Scan on history greathistory (cost=0.00..3562.81 rows=10435 width=17) (actual time=14.664..31.224 rows=10751 loops=1)
    Output: greathistory."historyID", greathistory."domainName", greathistory."historyTimestamp", greathistory."historyType", greathistory."historyTypeValue", greathistory."historyServer"
    Filter: (("domainName")::text ~~ 'w%'::text)
    
    Der Seq-Scan könnte durch einen passenden Index erschlagen werden, wie, zeigte ich schon. Das Hauptproblem ist aber der HashAggregate, der durch Dein GROUP BY kommt.

    Du kannst mal versuchen, die Abfrage so umzustellen:

    Code:
    select history.DomainName, foo.google_results 
    from history left join (
      select distinct on (historyTimestamp) 
        domainName, 
        historyType, 
        case when historyType=14 then historyTypeValue else null end as google_results 
      from history where historyType in (3,10,12,13,14,22,30) 
      and domainName like 'w%'
      order by historyTimestamp
    ) foo on history.DomainName=foo.DomainName;
    
    Ich habe Deine Tabelle so genommen, nur auf das Quoten der Spaltenbezeichner wegens Faulheit verzichtet, außerdem hab ich das nur für Typ 14 gemacht.

    Ob das schneller geht, kann ich aber nicht garantieren. Rückinfo von Dir wäre aber nett ...

    Andreas
    Ach ja, im äußeren auch noch WHERE benutzen:

    Code:
    select history.DomainName, foo.google_results from history left join (select distinct on (historyTimestamp) domainName, historyType, case when historyType=14 then historyTypeValue else null end as google_results from history where historyType in (3,10,12,13,14,22,30) and domainName like 'w%' order by historyTimestamp) foo on history.DomainName=foo.DomainName where history.historyType in (3,10,12,13,14,22,30) and history.domainName like 'w%';
    
    Andreas
    --
    Papa-Kind-Kur vom 8. bis 29. September ;-)
    PostgreSQL Hosting
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    http://www.amazon.de/gp/registry/2APKFICAIYMWB
    https://www.xing.com/profile/Andreas_Kretschmer7

  8. #8
    spinner0815 ist offline Neuer Benutzer
    Registriert seit
    15.03.2010
    Beiträge
    12

    Standard

    vielen dank schonmal.
    ich werde das morgen mal probieren und mcih dann nochmal melden.

    bis dann

  9. #9
    spinner0815 ist offline Neuer Benutzer
    Registriert seit
    15.03.2010
    Beiträge
    12

    Standard

    Also ich hab mir die query jetzt mal zusammengebastelt:
    Code:
    Select foo."domainName", foo.google_results, foo.dchk, foo.whois, foo.google_sitelinks, foo.google_pagerank, foo.google_backlinks
    from history left join 
    	(
    		select distinct on ("historyTimestamp") "domainName", "historyType",
    			case when "historyType"=14 then "historyTypeValue" else 
    				null end as google_results,
    			case when "historyType"=30 then "historyTypeValue" else
    				null end as dchk,
    			case when "historyType"=3 then "historyTypeValue" else 
    				null end as whois,
    			case when "historyType"=10 then "historyTypeValue" else
    				null end as alexaRank,
    			case when "historyType"=13 then "historyTypeValue" else 
    				null end as google_sitelinks,
    			case when "historyType"=22 then "historyTypeValue" else
    				null end as google_pagerank,
    			case when "historyType"=12 then "historyTypeValue" else
    				null end as google_backlinks
    		from history as his where "historyType" in (3,10,12,13,14,22,30) and "domainName" like 'w%'
    		order by "historyTimestamp"
    	) foo on history."domainName"=foo."domainName" 
    where history."historyType" in (3,10,12,13,14,22,30) and history."domainName" like 'w%' order by "domainName" desc;
    
    hier die analyse:

    Code:
    "Sort  (cost=11387.40..11400.54 rows=5258 width=80) (actual time=190.619..190.984 rows=10223 loops=1)"
    "  Output: foo."domainName", foo.google_results, foo.dchk, foo.whois, foo.google_sitelinks, foo.google_pagerank, foo.google_backlinks"
    "  Sort Key: foo."domainName""
    "  Sort Method:  quicksort  Memory: 970kB"
    "  ->  Merge Left Join  (cost=10957.63..11062.44 rows=5258 width=80) (actual time=126.421..138.121 rows=10223 loops=1)"
    "        Output: foo."domainName", foo.google_results, foo.dchk, foo.whois, foo.google_sitelinks, foo.google_pagerank, foo.google_backlinks"
    "        Merge Cond: ((history."domainName")::text = (foo."domainName")::text)"
    "        ->  Sort  (cost=5231.80..5244.95 rows=5258 width=17) (actual time=70.580..70.772 rows=4745 loops=1)"
    "              Output: history."domainName""
    "              Sort Key: history."domainName""
    "              Sort Method:  quicksort  Memory: 386kB"
    "              ->  Seq Scan on history  (cost=0.00..4906.85 rows=5258 width=17) (actual time=20.088..44.391 rows=4745 loops=1)"
    "                    Output: history."domainName""
    "                    Filter: ((("domainName")::text ~~ 'w%'::text) AND ("historyType" = ANY ('{3,10,12,13,14,22,30}'::integer[])))"
    "        ->  Sort  (cost=5725.83..5738.91 rows=5235 width=80) (actual time=55.833..56.320 rows=10223 loops=1)"
    "              Output: foo."domainName", foo.google_results, foo.dchk, foo.whois, foo.google_sitelinks, foo.google_pagerank, foo.google_backlinks"
    "              Sort Key: foo."domainName""
    "              Sort Method:  quicksort  Memory: 459kB"
    "              ->  Subquery Scan foo  (cost=5323.82..5402.46 rows=5235 width=80) (actual time=28.365..30.692 rows=4745 loops=1)"
    "                    Output: foo."domainName", foo.google_results, foo.dchk, foo.whois, foo.google_sitelinks, foo.google_pagerank, foo.google_backlinks"
    "                    ->  Unique  (cost=5323.82..5350.11 rows=5235 width=35) (actual time=28.364..29.913 rows=4745 loops=1)"
    "                          Output: his."domainName", his."historyType", (CASE WHEN (his."historyType" = 14) THEN his."historyTypeValue" ELSE NULL::double precision END), (CASE WHEN (his."historyType" = 30) THEN his."historyTypeValue" ELSE NULL::double precision END), (CASE WHEN (his."historyType" = 3) THEN his."historyTypeValue" ELSE NULL::double precision END), (CASE WHEN (his."historyType" = 10) THEN his."historyTypeValue" ELSE NULL::double precision END), (CASE WHEN (his."historyType" = 13) THEN his."historyTypeValue" ELSE NULL::double precision END), (CASE WHEN (his."historyType" = 22) THEN his."historyTypeValue" ELSE NULL::double precision END), (CASE WHEN (his."historyType" = 12) THEN his."historyTypeValue" ELSE NULL::double precision END)"
    "                          ->  Sort  (cost=5323.82..5336.96 rows=5258 width=35) (actual time=28.361..28.587 rows=4745 loops=1)"
    "                                Output: his."domainName", his."historyType", (CASE WHEN (his."historyType" = 14) THEN his."historyTypeValue" ELSE NULL::double precision END), (CASE WHEN (his."historyType" = 30) THEN his."historyTypeValue" ELSE NULL::double precision END), (CASE WHEN (his."historyType" = 3) THEN his."historyTypeValue" ELSE NULL::double precision END), (CASE WHEN (his."historyType" = 10) THEN his."historyTypeValue" ELSE NULL::double precision END), (CASE WHEN (his."historyType" = 13) THEN his."historyTypeValue" ELSE NULL::double precision END), (CASE WHEN (his."historyType" = 22) THEN his."historyTypeValue" ELSE NULL::double precision END), (CASE WHEN (his."historyType" = 12) THEN his."historyTypeValue" ELSE NULL::double precision END)"
    "                                Sort Key: his."historyTimestamp""
    "                                Sort Method:  quicksort  Memory: 506kB"
    "                                ->  Seq Scan on history his  (cost=0.00..4998.87 rows=5258 width=35) (actual time=8.444..25.020 rows=4745 loops=1)"
    "                                      Output: his."domainName", his."historyType", CASE WHEN (his."historyType" = 14) THEN his."historyTypeValue" ELSE NULL::double precision END, CASE WHEN (his."historyType" = 30) THEN his."historyTypeValue" ELSE NULL::double precision END, CASE WHEN (his."historyType" = 3) THEN his."historyTypeValue" ELSE NULL::double precision END, CASE WHEN (his."historyType" = 10) THEN his."historyTypeValue" ELSE NULL::double precision END, CASE WHEN (his."historyType" = 13) THEN his."historyTypeValue" ELSE NULL::double precision END, CASE WHEN (his."historyType" = 22) THEN his."historyTypeValue" ELSE NULL::double precision END, CASE WHEN (his."historyType" = 12) THEN his."historyTypeValue" ELSE NULL::double precision END"
    "                                      Filter: ((("domainName")::text ~~ 'w%'::text) AND ("historyType" = ANY ('{3,10,12,13,14,22,30}'::integer[])))"
    "Total runtime: 191.639 ms"
    
    ich denek mal schon dass das schneller ist nur leider kommt halt auch nich das gewünschte ergebnis und das ergebnis das erscheint drückt die geschwindigkeit gewaltig nach unten.
    Code:
    "domainName"   ; google_results; dchk; whois; google_sitelinks; google_pagerank; google_backlinks
    "wzrv.de"           ;10500            ;        ;         ;                        ;                         ;
    "wzonline.de"     ;95600            ;        ;         ;                        ;                         ;
    "wzkarten2.de"   ;2340             ;        ;         ;                        ;                         ;
    "wzkarten.de"     ;5140             ;        ;         ;                        ;                         ;
    "wzkarten.de"     ;5140             ;        ;         ;                        ;                         ;
    "wzkarten.de"     ;                    ;        ;   5    ;                        ;                          ;
    "wzkarten.de"     ;                    ;        ;   5    ;                        ;                          ;
    "wzim.de"          ;1300            ;        ;         ;                        ;                         ;
    "wzhh.de"          ;1280            ;        ;         ;                        ;                         ;
    
    Durch die Duplikate kommen bei der abfrage wohl die Geschwindigkeitsprobleme zustande. bei "wzkarte.de" wurde 2mal das google_result, zu unterschiedlichen Zeiten, geprüft. Nur leider hab ich keine Ahnung wie ich in der Abfrage nach der aktuellen Zeit fragen soll, die ja unter "historyTimestamp" hinterlegt ist.
    Wenn er nur die richtige Spalte, sprich, das google_results mit dem "neustem" "histryTimestamp" nehmen würde, dann würde da ja schon eine Zeile wegfallen.
    desweiteren kann ich nicht nach "domainName" gruppieren. Wenn ich es versuche verlangt er das ich die anderen werte (google_results, whois......) ebenfalls mit in das group by reinnehme.

    Habt ihr ne idee wie ich die 2 probleme lösen kann?

    p.s.: Die Tabelle sah im Editor besser aus
    Geändert von spinner0815 (16.03.2010 um 13:33 Uhr)

  10. #10
    akretschmer ist offline Erfahrener Benutzer
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    4.478

    Standard

    Zitat Zitat von spinner0815 Beitrag anzeigen
    Durch die Duplikate kommen bei der abfrage wohl die Geschwindigkeitsprobleme zustande. bei "wzkarte.de" wurde 2mal das google_result, zu unterschiedlichen Zeiten, geprüft. Nur leider hab ich keine Ahnung wie ich in der Abfrage nach der aktuellen Zeit fragen soll, die ja unter "historyTimestamp" hinterlegt ist.
    Wenn er nur die richtige Spalte, sprich, das google_results mit dem "neustem" "histryTimestamp" nehmen würde, dann würde da ja schon eine Zeile wegfallen.
    desweiteren kann ich nicht nach "domainName" gruppieren. Wenn ich es versuche verlangt er das ich die anderen werte (google_results, whois......) ebenfalls mit in das group by reinnehme.

    Habt ihr ne idee wie ich die 2 probleme lösen kann?

    p.s.: Die Tabelle sah im Editor besser aus
    Ja, Fehler von mir. Mach mal aus

    Code:
    select distinct on ("historyTimestamp") "domainName",
    
    ein

    Code:
    select distinct on ("domainName") "domainName", ... order by domainName, historyTimestamp desc
    
    Damit sollte "domainName" dann UNIQUE sein.

    Um Codeblöcke hier zu zeichen setze ein code und ein /code drum herum, in eckigen Klammern. Dann klappts auch mit der Tabelle ...

    Ach ja:

    Code:
    "              ->  Seq Scan on history  (cost=0.00..4906.85 rows=5258 width=17) (actual time=20.088..44.391 rows=4745 loops=1)"
    "                    Output: history."domainName""
    "                    Filter: ((("domainName")::text ~~ 'w%'::text) AND ("historyType" = ANY ('{3,10,12,13,14,22,30}'::integer[])))"
    
    ließe sich sicher zu einem Index-Scan überreden, wenn da ein Index wäre, mit text_pattern_ops, siehe mein Beispiel ;-)


    Andreas
    --
    Papa-Kind-Kur vom 8. bis 29. September ;-)
    PostgreSQL Hosting
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    http://www.amazon.de/gp/registry/2APKFICAIYMWB
    https://www.xing.com/profile/Andreas_Kretschmer7

+ Antworten
Seite 1 von 2 1 2 LetzteLetzte

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