Optimierung meiner Abfrage

Hallo liebe Gemeinde,

ich schreibe eine Webapplikation mit Djangound 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:

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



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.

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.

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? ANALYZEhabe 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 WITHist, 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

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 etwa596.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 EXPLAINgibt es hier: lfb | explain.depesz.com
Der aktuell verwendete Query ist folgender:

WITH items AS 
( [color=#008000]-- rekursiv nach Items suchen, die mit der Benutzereingabe verknüpft sind[/color]
    WITH recursive sub(id) AS 
    ( [color=#008000]-- untergeordnete Items suchen[/color]
        VALUES (3742),(2597),(3094),(2836) 
        UNION 
        SELECT from_item_id
        FROM groups g, sub 
        WHERE g.to_item_id = sub.id 
    ), 
    sup(id) AS 
    ( [color=#008000]-- übergeordnete Items suchen[/color]
        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
) 


[color=#008000]-- Links ausgeben (nach Anzahl zutreffender und nicht-angegebener Items sortiert)[/color]
SELECT t2.*, (2*important_hits + hits + itemcount - 2 * missingcount) AS score, 
    ARRAY [color=#008000]-- nicht angegebene Items dieses Links auflisten[/color]
    ( 
        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
            ( [color=#008000]-- nach bestimmten Tags filtern[/color]
                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!