Seite 1 von 2 12 LetzteLetzte
Ergebnis 1 bis 10 von 11

Thema: Maximalwerte

  1. #1
    strippenzieher ist offline Benutzer
    Registriert seit
    22.01.2011
    Beiträge
    57

    Standard Maximalwerte

    Hallo, ich habe folgende Tabelle:
    Code:
     authorid | bookid  | count 
    ----------+---------+-------
          526 |    1656 |  4274
          526 |    1716 |  2481
          526 |    4828 |   914
          526 |    4827 |   898
          526 |     824 |   729
       310973 |  772611 |   152
          526 |   50934 |   147
          526 |   21235 |    80
          526 |  566128 |    32
          526 | 1528159 |     7
       185299 |  446216 |     1
    und hätte für jede authorid die bookid mit dem höchsten count. Bei zwei Spalten wäre das mit dem max() ja überhaupt kein Problem, bei dreien weiß ich aber nicht mehr so ganz wie ich das elegant lösen kann...

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

    Standard

    Zitat Zitat von strippenzieher Beitrag anzeigen
    Hallo, ich habe folgende Tabelle:
    Code:
     authorid | bookid  | count 
    ----------+---------+-------
          526 |    1656 |  4274
          526 |    1716 |  2481
          526 |    4828 |   914
          526 |    4827 |   898
          526 |     824 |   729
       310973 |  772611 |   152
          526 |   50934 |   147
          526 |   21235 |    80
          526 |  566128 |    32
          526 | 1528159 |     7
       185299 |  446216 |     1
    und hätte für jede authorid die bookid mit dem höchsten count. Bei zwei Spalten wäre das mit dem max() ja überhaupt kein Problem, bei dreien weiß ich aber nicht mehr so ganz wie ich das elegant lösen kann...
    Was hast denn schon so alles versucht? *g*

    Code:
    test=*# select * from strippenzieher ;
     a | b | c
    ---+---+----
     1 | 2 | 10
     1 | 3 | 15
     1 | 4 | 20
     2 | 5 |  7
     2 | 5 | 77
     3 | 6 | 66
    (6 rows)
    
    Time: 0,278 ms
    test=*# select a.* from strippenzieher a right join (select a,max(c) as c from strippenzieher group by a) foo on (a.a, a.c)=(foo.a, foo.c);
     a | b | c
    ---+---+----
     1 | 4 | 20
     2 | 5 | 77
     3 | 6 | 66
    (3 rows)

    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
    strippenzieher ist offline Benutzer
    Registriert seit
    22.01.2011
    Beiträge
    57

    Standard

    hey Andreas,

    ich hatte mir eine temporäre Tabelle dessen erstellt was ich als Beispiel angegeben habe und diese so oft abgefragt wie es Autoren gibt. Dauert länger und ist umständlich. Aber auch bei deiner Lösung die ich erst mal nachvollziehen musste brauche ich die. In der postgresql-docu ist auch nur ein Beispiel in welchem ich die Beispieltabelle erst mal temporär speichern muss um sie dann zwei mal abzufragen (was definitiv schneller geht als die Abfrage zwei mal durchlaufen zu lassen. Ein

    select a.* from (select ...) as a right join (select a,max(c) as c from a group by a) foo on (a.a, a.c)=(foo.a, foo.c);

    scheint ja leider nicht zu funktionieren.

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

    Standard

    Zitat Zitat von strippenzieher Beitrag anzeigen
    hey Andreas,

    ich hatte mir eine temporäre Tabelle dessen erstellt was ich als Beispiel angegeben habe und diese so oft abgefragt wie es Autoren gibt. Dauert länger und ist umständlich. Aber auch bei deiner Lösung die ich erst mal nachvollziehen musste brauche ich die. In der postgresql-docu ist auch nur ein Beispiel in welchem ich die Beispieltabelle erst mal temporär speichern muss um sie dann zwei mal abzufragen (was definitiv schneller geht als die Abfrage zwei mal durchlaufen zu lassen. Ein

    select a.* from (select ...) as a right join (select a,max(c) as c from a group by a) foo on (a.a, a.c)=(foo.a, foo.c);

    scheint ja leider nicht zu funktionieren.
    Also mein Beispiel funktioniert schon, so wie auch

    Code:
    test=*# select a,b,c from (select a,b,c, row_number() over (partition by a order by c desc) from strippenzieher) foo where row_number = 1 ;
     a | b | c
    ---+---+----
     1 | 4 | 20
     2 | 5 | 77
     3 | 6 | 66
    (3 rows)
    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
    strippenzieher ist offline Benutzer
    Registriert seit
    22.01.2011
    Beiträge
    57

    Standard

    Ja, dein Beispiel hat funktioniert - dieses nun wohl sogar noch viel besser als mit einer temporären Tabelle. Ich muss nur erstmal durchsteigen...

  6. #6
    strippenzieher ist offline Benutzer
    Registriert seit
    22.01.2011
    Beiträge
    57

    Standard

    Zitat Zitat von akretschmer Beitrag anzeigen
    Also mein Beispiel funktioniert schon, so wie auch

    Code:
    test=*# select a,b,c from (select a,b,c, row_number() over (partition by  a order by c desc) from strippenzieher) foo where row_number = 1 ;
     a | b | c
    ---+---+----
     1 | 4 | 20
     2 | 5 | 77
     3 | 6 | 66
    (3 rows)
    Andreas
    hey - ich hab die anfrage letztendlich doch ein wenig anders gestaltet:
    SELECT DISTINCT on (b.a) b.a, b.b, count(c.e) FROM b, c inner join e using (e) WHERE (b.a = 0 OR b.a = 211070 OR b.a = 2 OR b.a = 509419) AND b.b = e.b GROUP BY b.a, b.b order by b.a, count(c.e) desc;
    was ebenfalls funktioniert - aber genauso langsam wie alle anderen Formulierungen.

    Ausgeschrieben sieht das dann so aus:
    SELECT DISTINCT on (ba.authorid) ba.authorid, ba.bookid, count(userbookid) FROM book_author ba, userbooks ub inner join editions e using (editionid) WHERE (ba.authorid = 0 OR ba.authorid = 211070 OR ba.authorid = 2 OR ba.authorid = 509419) AND ba.bookid = e.bookid GROUP BY ba.authorid, ba.bookid order by ba.authorid,count desc;

    und ein EXPLAIN ANASYSE bringt folgendes zu Tage:
    Code:
                                                                                 QUERY  PLAN                                                                              
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Unique  (cost=169307.48..169307.84 rows=73 width=12) (actual time=10163.554..10163.641 rows=2 loops=1)
       ->  Sort  (cost=169307.48..169307.66 rows=73 width=12) (actual time=10163.551..10163.592 rows=65 loops=1)
             Sort Key: ba.authorid, (count(ub.userbookid))
             Sort Method:  quicksort  Memory: 28kB
             ->  HashAggregate  (cost=169304.31..169305.22 rows=73 width=12) (actual time=10163.249..10163.312 rows=65 loops=1)
                   ->  Hash Join  (cost=12253.51..169288.87 rows=2058 width=12) (actual time=18.367..10160.866 rows=1091 loops=1)
                         Hash Cond: (ub.editionid = e.editionid)
                          ->  Seq Scan on userbooks ub  (cost=0.00..109632.02 rows=6317702  width=8) (actual time=0.017..5134.376 rows=6316408 loops=1)
                         ->  Hash  (cost=12205.12..12205.12 rows=3871 width=12) (actual time=11.236..11.236 rows=2095 loops=1)
                                ->  Nested Loop  (cost=18.57..12205.12 rows=3871 width=12) (actual  time=0.116..9.208 rows=2095 loops=1)
                                      ->  Bitmap Heap Scan on book_author ba  (cost=18.57..293.04 rows=73  width=8) (actual time=0.088..0.931 rows=139 loops=1)
                                            Recheck Cond: ((authorid = 0) OR (authorid = 211070) OR (authorid = 2)  OR (authorid = 509419))
                                           ->   BitmapOr  (cost=18.57..18.57 rows=73 width=0) (actual time=0.067..0.067  rows=0 loops=1)
                                                 ->   Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18 width=0)  (actual time=0.012..0.012 rows=0 loops=1)
                                                       Index Cond: (authorid = 0)
                                                  ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.018..0.018 rows=3 loops=1)
                                                       Index Cond: (authorid = 211070)
                                                  ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.018..0.018 rows=135 loops=1)
                                                       Index Cond: (authorid = 2)
                                                  ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.013..0.013 rows=1 loops=1)
                                                       Index Cond: (authorid = 509419)
                                      ->  Index Scan using index_bookid on editions e  (cost=0.00..162.52  rows=53 width=8) (actual time=0.014..0.037 rows=15 loops=139)
                                           Index Cond: (e.bookid = ba.bookid)
     Total runtime: 10163.739 ms
    (24 Zeilen)
    Bei Vermeidung von sequentiellen Scans durch 'SET enable_seqscan TO off;' wird die Abfragezeit jedoch deutlich vermindert:

    explain analyse SELECT DISTINCT on (ba.authorid) ba.authorid, ba.bookid, count(userbookid) FROM book_author ba, userbooks ub inner join editions e using (editionid) WHERE (ba.authorid = 0 OR ba.authorid = 211070 OR ba.authorid = 2 OR ba.authorid = 509419) AND ba.bookid = e.bookid GROUP BY ba.authorid, ba.bookid order by ba.authorid,count desc;
    Code:
                                                                              QUERY  PLAN                                                                           
    -------------------------------------------------------------------------------------------------------------------------------------------------------------
     Unique  (cost=651614.26..651614.63 rows=73 width=12) (actual time=23.733..23.815 rows=2 loops=1)
       ->  Sort  (cost=651614.26..651614.45 rows=73 width=12) (actual time=23.730..23.769 rows=65 loops=1)
             Sort Key: ba.authorid, (count(ub.userbookid))
             Sort Method:  quicksort  Memory: 28kB
             ->  HashAggregate  (cost=651611.09..651612.00 rows=73 width=12) (actual time=23.616..23.662 rows=65 loops=1)
                   ->  Nested Loop  (cost=18.57..651595.58 rows=2068 width=12) (actual time=0.164..22.629 rows=1091 loops=1)
                         ->  Nested Loop  (cost=18.57..12205.24 rows=3874 width=12) (actual time=0.120..8.117 rows=2095 loops=1)
                                ->  Bitmap Heap Scan on book_author ba  (cost=18.57..293.05 rows=73  width=8) (actual time=0.093..0.763 rows=139 loops=1)
                                     Recheck Cond: ((authorid = 0) OR (authorid = 211070) OR (authorid = 2) OR (authorid = 509419))
                                     ->  BitmapOr  (cost=18.57..18.57 rows=73 width=0) (actual time=0.069..0.069 rows=0 loops=1)
                                            ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.025..0.025 rows=0 loops=1)
                                                 Index Cond: (authorid = 0)
                                            ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.013..0.013 rows=3 loops=1)
                                                 Index Cond: (authorid = 211070)
                                            ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.015..0.015 rows=135 loops=1)
                                                 Index Cond: (authorid = 2)
                                            ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.012..0.012 rows=1 loops=1)
                                                 Index Cond: (authorid = 509419)
                                ->  Index Scan using index_bookid on editions e  (cost=0.00..162.52  rows=53 width=8) (actual time=0.011..0.031 rows=15 loops=139)
                                     Index Cond: (e.bookid = ba.bookid)
                          ->  Index Scan using index_editionid on userbooks ub   (cost=0.00..164.50 rows=44 width=8) (actual time=0.003..0.005 rows=1  loops=2095)
                               Index Cond: (ub.editionid = e.editionid)
     Total runtime: 23.913 ms
    (23 Zeilen)
    Nun meine Frage: Wie kommt sowas? Ist der Planer zu ungenau? Muss ich das nun bei jeder einzelnen Abfrage überprüfen? Oder habe ich generell etwas nicht beachtet bzw. falsch eingestellt?

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

    Standard

    Zitat Zitat von strippenzieher Beitrag anzeigen
    hey - ich hab die anfrage letztendlich doch ein wenig anders gestaltet:
    SELECT DISTINCT on (b.a) b.a, b.b, count(c.e) FROM b, c inner join e using (e) WHERE (b.a = 0 OR b.a = 211070 OR b.a = 2 OR b.a = 509419) AND b.b = e.b GROUP BY b.a, b.b order by b.a, count(c.e) desc;
    was ebenfalls funktioniert - aber genauso langsam wie alle anderen Formulierungen.

    Ausgeschrieben sieht das dann so aus:
    SELECT DISTINCT on (ba.authorid) ba.authorid, ba.bookid, count(userbookid) FROM book_author ba, userbooks ub inner join editions e using (editionid) WHERE (ba.authorid = 0 OR ba.authorid = 211070 OR ba.authorid = 2 OR ba.authorid = 509419) AND ba.bookid = e.bookid GROUP BY ba.authorid, ba.bookid order by ba.authorid,count desc;

    und ein EXPLAIN ANASYSE bringt folgendes zu Tage:
    Code:
                                                                                 QUERY  PLAN                                                                              
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Unique  (cost=169307.48..169307.84 rows=73 width=12) (actual time=10163.554..10163.641 rows=2 loops=1)
       ->  Sort  (cost=169307.48..169307.66 rows=73 width=12) (actual time=10163.551..10163.592 rows=65 loops=1)
             Sort Key: ba.authorid, (count(ub.userbookid))
             Sort Method:  quicksort  Memory: 28kB
             ->  HashAggregate  (cost=169304.31..169305.22 rows=73 width=12) (actual time=10163.249..10163.312 rows=65 loops=1)
                   ->  Hash Join  (cost=12253.51..169288.87 rows=2058 width=12) (actual time=18.367..10160.866 rows=1091 loops=1)
                         Hash Cond: (ub.editionid = e.editionid)
                          ->  Seq Scan on userbooks ub  (cost=0.00..109632.02 rows=6317702  width=8) (actual time=0.017..5134.376 rows=6316408 loops=1)
                         ->  Hash  (cost=12205.12..12205.12 rows=3871 width=12) (actual time=11.236..11.236 rows=2095 loops=1)
                                ->  Nested Loop  (cost=18.57..12205.12 rows=3871 width=12) (actual  time=0.116..9.208 rows=2095 loops=1)
                                      ->  Bitmap Heap Scan on book_author ba  (cost=18.57..293.04 rows=73  width=8) (actual time=0.088..0.931 rows=139 loops=1)
                                            Recheck Cond: ((authorid = 0) OR (authorid = 211070) OR (authorid = 2)  OR (authorid = 509419))
                                           ->   BitmapOr  (cost=18.57..18.57 rows=73 width=0) (actual time=0.067..0.067  rows=0 loops=1)
                                                 ->   Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18 width=0)  (actual time=0.012..0.012 rows=0 loops=1)
                                                       Index Cond: (authorid = 0)
                                                  ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.018..0.018 rows=3 loops=1)
                                                       Index Cond: (authorid = 211070)
                                                  ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.018..0.018 rows=135 loops=1)
                                                       Index Cond: (authorid = 2)
                                                  ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.013..0.013 rows=1 loops=1)
                                                       Index Cond: (authorid = 509419)
                                      ->  Index Scan using index_bookid on editions e  (cost=0.00..162.52  rows=53 width=8) (actual time=0.014..0.037 rows=15 loops=139)
                                           Index Cond: (e.bookid = ba.bookid)
     Total runtime: 10163.739 ms
    (24 Zeilen)
    Bei Vermeidung von sequentiellen Scans durch 'SET enable_seqscan TO off;' wird die Abfragezeit jedoch deutlich vermindert:

    explain analyse SELECT DISTINCT on (ba.authorid) ba.authorid, ba.bookid, count(userbookid) FROM book_author ba, userbooks ub inner join editions e using (editionid) WHERE (ba.authorid = 0 OR ba.authorid = 211070 OR ba.authorid = 2 OR ba.authorid = 509419) AND ba.bookid = e.bookid GROUP BY ba.authorid, ba.bookid order by ba.authorid,count desc;
    Code:
                                                                              QUERY  PLAN                                                                           
    -------------------------------------------------------------------------------------------------------------------------------------------------------------
     Unique  (cost=651614.26..651614.63 rows=73 width=12) (actual time=23.733..23.815 rows=2 loops=1)
       ->  Sort  (cost=651614.26..651614.45 rows=73 width=12) (actual time=23.730..23.769 rows=65 loops=1)
             Sort Key: ba.authorid, (count(ub.userbookid))
             Sort Method:  quicksort  Memory: 28kB
             ->  HashAggregate  (cost=651611.09..651612.00 rows=73 width=12) (actual time=23.616..23.662 rows=65 loops=1)
                   ->  Nested Loop  (cost=18.57..651595.58 rows=2068 width=12) (actual time=0.164..22.629 rows=1091 loops=1)
                         ->  Nested Loop  (cost=18.57..12205.24 rows=3874 width=12) (actual time=0.120..8.117 rows=2095 loops=1)
                                ->  Bitmap Heap Scan on book_author ba  (cost=18.57..293.05 rows=73  width=8) (actual time=0.093..0.763 rows=139 loops=1)
                                     Recheck Cond: ((authorid = 0) OR (authorid = 211070) OR (authorid = 2) OR (authorid = 509419))
                                     ->  BitmapOr  (cost=18.57..18.57 rows=73 width=0) (actual time=0.069..0.069 rows=0 loops=1)
                                            ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.025..0.025 rows=0 loops=1)
                                                 Index Cond: (authorid = 0)
                                            ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.013..0.013 rows=3 loops=1)
                                                 Index Cond: (authorid = 211070)
                                            ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.015..0.015 rows=135 loops=1)
                                                 Index Cond: (authorid = 2)
                                            ->  Bitmap Index Scan on authorid1  (cost=0.00..4.63 rows=18  width=0) (actual time=0.012..0.012 rows=1 loops=1)
                                                 Index Cond: (authorid = 509419)
                                ->  Index Scan using index_bookid on editions e  (cost=0.00..162.52  rows=53 width=8) (actual time=0.011..0.031 rows=15 loops=139)
                                     Index Cond: (e.bookid = ba.bookid)
                          ->  Index Scan using index_editionid on userbooks ub   (cost=0.00..164.50 rows=44 width=8) (actual time=0.003..0.005 rows=1  loops=2095)
                               Index Cond: (ub.editionid = e.editionid)
     Total runtime: 23.913 ms
    (23 Zeilen)
    Nun meine Frage: Wie kommt sowas? Ist der Planer zu ungenau? Muss ich das nun bei jeder einzelnen Abfrage überprüfen? Oder habe ich generell etwas nicht beachtet bzw. falsch eingestellt?
    Du könntest mal versuchsweise random_page_cost verringern, um auf diesem Wege Indexscans zu bevorzugen.

    Und ja, ein ganz heißer Hinweis: zur PGCONF.DE wird es sehr wahrscheinlich mehr als einen Vortrag zum Thema Optimierung geben. Hört man zumindest aus gewöhnlich gut informierten Kreisen ...


    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
    strippenzieher ist offline Benutzer
    Registriert seit
    22.01.2011
    Beiträge
    57

    Standard

    Mit folgenden Werten scheine ich ganz gut hinzukommen:

    seq_page_cost = 8.0 #von ursrünglich 2
    random_page_cost = 2.0 #von ursprünglich 4

    Vielen Dank!

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

    Standard

    Zitat Zitat von strippenzieher Beitrag anzeigen
    Mit folgenden Werten scheine ich ganz gut hinzukommen:

    seq_page_cost = 8.0 #von ursrünglich 2
    random_page_cost = 2.0 #von ursprünglich 4

    Vielen Dank!
    seq_page_cost sind 'normalerweise' auf 1. Egal, paß halt auf, daß nicht andere Pläne nun völlig kippen. Weil - so wie es nun ist, sind fortgesetzte Lesezugriffe, wo also die Platte schön nacheinander lesen kann ohne den Kopf zu bewegen und auf den passenden Sektor zu warten 4 mal teuer als willkürliches Lesen zufälliger Sektoren. In der Realität mechanischer Festplatten kommt dies eher extrem selten vor ...


    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
    strippenzieher ist offline Benutzer
    Registriert seit
    22.01.2011
    Beiträge
    57

    Standard

    Es sollte doch möglich sein, eine Beispieldatenbank zu bauen, mit der man mit vorgefertigten Queries die optimalen Werte für bestimmte Hardware herausbekommen kann. Gibt es da Ansätze?
    Ansonsten läuft bisher so ziemlich alles gefühlt schneller. Nur ist das natürlich alles nur Pi*Daumen. Irgendwie unbefriedigend

Seite 1 von 2 12 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