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.