Ergebnis 1 bis 5 von 5

Thema: Optimierung meiner Abfrage

  1. #1
    jnns ist offline Neuer Benutzer
    Registriert seit
    08.05.2010
    Beiträge
    7

    Frage Optimierung meiner Abfrage

    Hallo liebe Gemeinde,

    ich schreibe eine Webapplikation mit Django und muss für eine komplizierte Abfrage auf direkte SQL-Anweisungen zurückgreifen. Die Abfrage seht ihr unten; sie liefert dem Benutzer mehrere Links zurück, die nach einer Trefferquote der eingegebenen Kriterien sortiert werden.

    Die Tabelle, die abgefragt wird, hat momentan um die 60.000 Einträge, soll allerdings auch noch umfangreicher werden. Zur Zeit dauert der Query ca. 3-5 Sekunden - je nach Anzahl und Verknüpfungen der eingegebenen Kriterien. Mein Ziel ist es nun, die Dauer der Abfrage auf benutzerfreundliche 0-1 Sekunden zu drücken.

    Der Server, um den es sich handelt, läuft mit Debian und 8 GB RAM. Ziemlich leistungsstark für meine Ansprüche, schätze ich. Relevante Konfigurationsparameter seht ihr hier:
    Code:
    max_connections = 100
    shared_buffers = 3GB
    work_mem = 40MB
    maintenance_work_mem = 1GB
    cpu_tuple_cost = 0.003
    cpu_index_tuple_cost = 0.001
    cpu_operator_cost = 0.0005
    effective_cache_size = 4GB
    autovacuum = on
    Code:
    with counts as (
        with missing_items as (
            with hits_and_misses as (
                with items as (
                    -- Items suchen, die den Suchbegriffen untergeordnet sind
                    with recursive subitems(id) as (
                        values (2895), (3429), (3494), (3066), (3034) -- Benutzereingaben per POST
                      union
                        select groups.from_item_id from subitems
                        inner join item_groups groups on subitems.id = groups.to_item_id
                    ),
                    -- Items suchen, die den Suchbegriffen übergeordnet sind
                    superitems(id) as ( 
                        values (2895), (3429), (3494), (3066), (3034) -- Benutzereingaben per POST
                      union
                        select groups.to_item_id from superitems 
                        inner join item_groups groups on superitems.id = groups.from_item_id
                    )
                    select id from subitems union select id from superitems
                )
                -- Links auslesen
                select link.id, link.name, link.image, link.url, count(*) as hits,
                    -- Items auslesen, die dem Link zugeordnet sind, aber sich 
                    -- nicht in der Benutzereingabe befanden
                    array(                        
                        select missing_li.item_text
                        from linkitem missing_li
                        where missing_li.item_id not in (select id from items)
                        and missing_li.link_id = link.id
                    ) as missing
                from link as link
                inner join linkitem li on li.link_id = link.id
                where li.item_id in (select id from items)
                and link.status = 'a' -- Nur aktive Links zurückgeben
                group by link.id, link.name, link.image, link.url 
            )
            select *, coalesce(array_length(missing, 1), 0) as misses from hits_and_misses
        )
        select *, hits + misses as item_count,
            -- Auslesen der Schlagworte
            array(
                select slug
                from taggit_tag t
                inner join taggit_taggeditem ti on (ti.object_id = missing_items.id and content_type_id = 23)
                where ti.tag_id = t.id    
            ) as tags
        from missing_items
    )
    select * from counts
    order by %(input_token_count)s - hits + misses asc, misses asc, item_count desc
    limit 100
    Da die Ausgabe von EXPLAIN ANALYZE zu lang für einen Forenbeitrag ist habe ich sie extern verlinkt: 4m5 | explain.depesz.com

    Falls ihr Ideen habt, wie ich der Abfrage auf die Sprünge helfen könnte, wäre ich euch sehr dankbar.

  2. #2
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.055

    Standard

    Ist ja schön, das du so viele WITH benutzt - aber wundert es dich nicht, das du da diverse sequentielle Scans drin hast, die dann über weitere komplizierte Methoden und teure LECT JOINs gejoint werden?
    Vielleicht solltest du mal probieren, die kostenintensiven Sachen aus den Unterabfragen herauszubekommen und statt dessen eine mehr lineare Abfrage zu bauen.
    In deiner Anfrage sehe ich jetzt nichts, was zwingend WITH - und schon gar nicht in der Anzahl - voraussetzt.
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  3. #3
    jnns ist offline Neuer Benutzer
    Registriert seit
    08.05.2010
    Beiträge
    7

    Standard

    Die sequentiellen Scans sind mir zwar aufgefallen, aber nachdem ich testweise set enable_seqscans = off; gesetzt hatte und nur dann die Indizes genutzt wurden, dachte ich mir, dass der Planer schon weiß was er tut. Was könnte der Grund sein, dass er die Indizes nicht freiwillig heranzieht? ANALYZE habe ich sicherheitshalber durchgeführt.

    Die WITH Anweisungen habe ich angewendet, weil ich keine andere Möglichkeit für das rekursive Suchen der Items, die mit der Benutzereingabe verknüpft sind, sah. Siehst du einen Weg das eleganter zu lösen? Der zweite Grund für WITH ist, dass ich nicht weiß, wie ich mir sowohl den Inhalt des String-Arrays missing, als auch dessen Länge zurückgeben lassen kann ohne dass die Erzeugung des Arrays zweimal geschehen muss.

    Ich danke dir für dein Hilfe!

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

    Standard

    Zitat Zitat von jnns Beitrag anzeigen
    Die sequentiellen Scans sind mir zwar aufgefallen, aber nachdem ich testweise set enable_seqscans = off; gesetzt hatte und nur dann die Indizes genutzt wurden, dachte ich mir, dass der Planer schon weiß was er tut. Was könnte der Grund sein, dass er die Indizes nicht freiwillig heranzieht? ANALYZE habe ich sicherheitshalber durchgeführt.

    Die WITH Anweisungen habe ich angewendet, weil ich keine andere Möglichkeit für das rekursive Suchen der Items, die mit der Benutzereingabe verknüpft sind, sah. Siehst du einen Weg das eleganter zu lösen? Der zweite Grund für WITH ist, dass ich nicht weiß, wie ich mir sowohl den Inhalt des String-Arrays missing, als auch dessen Länge zurückgeben lassen kann ohne dass die Erzeugung des Arrays zweimal geschehen muss.

    Ich danke dir für dein Hilfe!
    Ist denn die Abfrage schneller, wenn du seq-scans verbietest?

    Zu WITH: keine Frage, nettes Feature. Aber auch ein neues. Möglicherweise werden Abfragen damit in späteren Versionen besser optimiert sein...

    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
    jnns ist offline Neuer Benutzer
    Registriert seit
    08.05.2010
    Beiträge
    7

    Standard

    Die abgefragten Tabellen sind mittlerweile nochmal beträchtlich gewachsen und ich habe versucht, die Abfrage etwas linearer zu gestalten, in dem ich auf die unnötigen WITHs verzichtet habe.
    Die Tabelle linkitem hat etwa 4.854.030 Zeilen, die Tabelle link etwa 596.112 Zeilen. Normalerweise dauert eine Abfrage um die 5 Sekunden. Es werden zwar immer noch sequentielle Scans durchgeführt, aber wenn ich Postgres zwinge, die Indizes zu nutzen, dauert die Abfrage noch länger.

    Ich würde gerne wissen, ob ich mit dieser Abfrage schon auf dem richtigen Weg bin und ob man da noch etwas optimieren kann -- oder ob es sinnvoll ist, dem Server mehr Arbeitsspeicher zu verpassen. Wenn ja, in welcher Größenordnung?

    Die Ausgabe von EXPLAIN gibt es hier: lfb | explain.depesz.com
    Der aktuell verwendete Query ist folgender:
    Code:
    WITH items AS 
    ( -- rekursiv nach Items suchen, die mit der Benutzereingabe verknüpft sind
        WITH recursive sub(id) AS 
        ( -- untergeordnete Items suchen
            VALUES (3742),(2597),(3094),(2836) 
            UNION 
            SELECT from_item_id
            FROM groups g, sub 
            WHERE g.to_item_id = sub.id 
        ), 
        sup(id) AS 
        ( -- übergeordnete Items suchen
            VALUES (3742),(2597),(3094),(2836) 
            UNION 
            SELECT to_item_id 
            FROM groups g, sup 
            WHERE g.from_item_id = sup.id
        ) 
        SELECT id FROM sub UNION SELECT id FROM sup
    ) 
    
    
    -- Links ausgeben (nach Anzahl zutreffender und nicht-angegebener Items sortiert)
    SELECT t2.*, (2*important_hits + hits + itemcount - 2 * missingcount) AS score, 
        ARRAY -- nicht angegebene Items dieses Links auflisten
        ( 
            SELECT item_text 
            FROM linkitem 
            WHERE verification = true 
                AND link_id = t2.id 
                AND item_id NOT IN (SELECT id FROM items) 
        ) as missing 
    FROM 
    ( 
        SELECT *, itemcount - hits AS missingcount 
        FROM 
        ( 
            SELECT * FROM 
            ( 
                SELECT l.id, 
                    COUNT(*) OVER w AS itemcount, 
                    SUM(CASE WHEN item_id IN (SELECT id FROM items) 
                        THEN 1 ELSE 0 END) over w AS hits, 
                    SUM(CASE WHEN item_id IN (VALUES (3742),(2597),(3094),(2836)) 
                        THEN 1 ELSE 0 END) over w AS important_hits
                FROM linkitem li 
                INNER JOIN link l ON (l.id = li.link_id AND verification = true AND l.status = 'a')
                WHERE EXISTS
                ( -- nach bestimmten Tags filtern
                    SELECT * FROM taggit_taggeditem ti 
                    INNER JOIN taggit_tag t 
                    ON (t.id = ti.tag_id AND t.slug IN ('tag1','tag2','tag3') 
                    AND ti.object_id = l.id 
                    AND ti.content_type_id = 23 
                    AND t.slug NOT IN ('tag4')) 
                ) WINDOW w AS (PARTITION BY li.link_id)
            ) t0 
            GROUP BY t0.id, itemcount, hits, important_hits 
        ) t1 
        WHERE hits > 3 
    ) t2 
    ORDER BY score DESC, missingcount ASC
    Ich danke euch vielmals für eure Mühe!

Ähnliche Themen

  1. Antworten: 4
    Letzter Beitrag: 31.01.2012, 16:55
  2. Optimierung bei 8GB
    Von traxanos im Forum Konfiguration
    Antworten: 1
    Letzter Beitrag: 02.07.2009, 21:55
  3. Problem UPDATE Optimierung
    Von DBGTMaster im Forum Konfiguration
    Antworten: 10
    Letzter Beitrag: 30.05.2008, 16:51
  4. Umlaute in meiner Tabelle inkorrekt
    Von Kai-Behncke im Forum Allgemeines
    Antworten: 6
    Letzter Beitrag: 15.01.2008, 15:26
  5. Algebraische Optimierung in PostgreSQL
    Von MartinL im Forum Verschiedenes
    Antworten: 0
    Letzter Beitrag: 05.12.2007, 09:02

Stichworte

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