Korrekt. Häufige Anwendergruppe sind Firmen aus dem Finanzumfeld (mit synchroner Multimaster) und Firmen mit weltweit verteilten Multimaster-Datenbanken (asynchron). Wer es braucht, kann es dann auch bezahlen.
Genau, meine Anweisung ist nicht allgemeingültig, das heißt ich muss für jede Tabelle die id wieder raussuchen und nach RETURNING schreiben.
Welches Problem willst Du damit lösen?
Ich habe 2 Millionen Datensätze(die mit anderen Tabellen verknüpft sind, wie z.B. tags), die durchsucht und sortiert werden sollen.
Meine Suche braucht 200 Sekunden, wenn die Suche etwas kompliziert ist:
impf schäden|nebenwirkung|adverse sprache=Deutsch -musik -computerspiele -gwup -corona -Spiele -covid -bild.de -Englisch -kunst -Paraodie -....
Binaural Beats -Englisch -Musik -Spiele -Parodie -....
Wie man sieht habe ich relativ viele Auschliesungsklauseln, die wichtig sind, um genau das zu suchen, was haben möchte. Denn wenn man z.B. nach Bineurale Beats/Töne sucht, findet man bei herkömmlichen Suchmachinen so viel Musik, das man gar keine Lust hat weiter zu suchen ein informatives Video zu finden.
Eine Tabelle mit 2 Millionen Datensätzen ist praktisch gar nichts. Wenn ich Performance Tests machen, fange ich gar nicht erst mit weniger an. Dafür braucht man mit keine verteilte Datenbank, und mit Sicherheit keine multi-master Lösung.
Wenn Du uns die tatsächlichen Abfragen zeigst, dann können wir da vermutlich helfen. Aber für das was Du da beschreibst ist eine relationale Datenbank vielleicht gar nicht die richtige Lösung. Ein System wie Solr oder ElasticSearch scheint da die bessere Wahl zu sein.
Wie gesagt, bei einer einfachen Suche ist das auch kein Problem, aber bei einer komplizierten Suche + nach Zeit/id Ordnen dauert es sehr lange.
MultiMaster ist auf sowas exakt gar keine Lösung. Tabellenstruktur, Abfrage, Ausführungsplan wäre hilfreich.
1 „Gefällt mir“
Wenn Du mehr Hardware auf das Problem werfen willst, dann solltest Du das mit einem Rechner machen der mehr CPUs, mehr Festplatten und mehr Hauptspeicher hat.
Hier eine einfache Suche:
Suche:impf schäden|nebenwirkung|adversive Ausschluss:game musik gwup englisch bild.de letsplay
SQL:
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
SELECT * FROM table_video WHERE NOT (((((( ( remarks IS NOT NULL AND remarks ILIKE '%game%' OR table_video.name IS NOT NULL AND table_video.name ILIKE '%game%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%game%' GROUP BY id)::bigint[] && tag_ids ))
OR ( remarks IS NOT NULL AND remarks ILIKE '%musik%' OR table_video.name IS NOT NULL AND table_video.name ILIKE '%musik%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%musik%' GROUP BY id)::bigint[] && tag_ids ))
OR ( remarks IS NOT NULL AND remarks ILIKE '%gwup%' OR table_video.name IS NOT NULL AND table_video.name ILIKE '%gwup%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%gwup%' GROUP BY id)::bigint[] && tag_ids ))
OR ( remarks IS NOT NULL AND remarks ILIKE '%englisch%' OR table_video.name IS NOT NULL AND table_video.name ILIKE '%englisch%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%englisch%' GROUP BY id)::bigint[] && tag_ids ))
OR ( remarks IS NOT NULL AND remarks ILIKE '%bild.de%' OR table_video.name IS NOT NULL AND table_video.name ILIKE '%bild.de%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%bild.de%' GROUP BY id)::bigint[] && tag_ids ))
OR ( remarks IS NOT NULL AND remarks ILIKE '%letsplay%' OR table_video.name IS NOT NULL AND table_video.name ILIKE '%letsplay%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%letsplay%' GROUP BY id)::bigint[] && tag_ids ))
AND (( ( remarks ILIKE '%impf%' OR table_video.name ILIKE '%impf%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%impf%' GROUP BY id)::bigint[] && tag_ids ))
AND ( remarks ILIKE '%schäden%' OR table_video.name ILIKE '%schäden%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%schäden%' GROUP BY id)::bigint[] && tag_ids OR remarks ILIKE '%nebenwirkung%' OR table_video.name ILIKE
'%nebenwirkung%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%nebenwirkung%' GROUP BY id)::bigint[] && tag_ids OR remarks ILIKE '%adversive%' OR table_video.name ILIKE '%adversive%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%adversive%' GROUP BY id)::bigint[] && tag_ids )) ORDER BY ID LIMIT 38
Ergebnis:
"QUERY PLAN"
"[
{
""Plan"": {
""Node Type"": ""Limit"",
""Startup Cost"": 36279.29,
""Total Cost"": 36279.29,
""Plan Rows"": 1,
""Plan Width"": 384,
""Actual Startup Time"": 4880.841,
""Actual Total Time"": 4880.841,
""Actual Rows"": 0,
""Actual Loops"": 1,
""Output"": [""table_video.id"", ""table_video.name"", ""table_video.date_time"", ""table_video.remarks"", ""table_video.source_site"", ""table_video.pic"", ""table_video.language_text"", ""table_video.play_url"", ""table_video.updated"", ""table_video.type_id"", ""table_video.tags"", ""table_video.duration"", ""table_video.media_url"", ""table_video.category_ids"", ""table_video.tag_ids""],
""Shared Hit Blocks"": 3,
""Shared Read Blocks"": 0,
""Shared Dirtied Blocks"": 0,
""Shared Written Blocks"": 0,
""Local Hit Blocks"": 0,
""Local Read Blocks"": 0,
""Local Dirtied Blocks"": 0,
""Local Written Blocks"": 0,
""Temp Read Blocks"": 0,
""Temp Written Blocks"": 0,
""Plans"": [
{
""Node Type"": ""Remote Subquery Scan"",
""Parent Relationship"": ""InitPlan"",
""Subplan Name"": ""InitPlan 1 (returns $0)"",
""Replicated"": ""no"",
""Node List"": [""datanode1"", ""datanode2""],
""Startup Cost"": 3625.12,
""Total Cost"": 3625.31,
""Plan Rows"": 19,
""Plan Width"": 8,
""Actual Startup Time"": 98.579,
""Actual Total Time"": 160.191,
""Actual Rows"": 380,
""Actual Loops"": 1,
""Output"": [""table_tag.id""],
""Shared Hit Blocks"": 0,
""Shared Read Blocks"": 0,
""Shared Dirtied Blocks"": 0,
""Shared Written Blocks"": 0,
""Local Hit Blocks"": 0,
""Local Read Blocks"": 0,
""Local Dirtied Blocks"": 0,
""Local Written Blocks"": 0,
""Temp Read Blocks"": 0,
""Temp Written Blocks"": 0
},
{
""Node Type"": ""Remote Subquery Scan"",
""Parent Relationship"": ""InitPlan"",
""Subplan Name"": ""InitPlan 2 (returns $1)"",
""Replicated"": ""no"",
""Node List"": [""datanode1"", ""datanode2""],
""Startup Cost"": 3625.12,
""Total Cost"": 3625.31,
""Plan Rows"": 19,
""Plan Width"": 8,
""Actual Startup Time"": 85.433,
""Actual Total Time"": 159.354,
""Actual Rows"": 1,
""Actual Loops"": 1,
""Output"": [""table_tag_1.id""],
""Shared Hit Blocks"": 0,
""Shared Read Blocks"": 0,
""Shared Dirtied Blocks"": 0,
""Shared Written Blocks"": 0,
""Local Hit Blocks"": 0,
""Local Read Blocks"": 0,
""Local Dirtied Blocks"": 0,
""Local Written Blocks"": 0,
""Temp Read Blocks"": 0,
""Temp Written Blocks"": 0
},
{
""Node Type"": ""Remote Subquery Scan"",
""Parent Relationship"": ""InitPlan"",
""Subplan Name"": ""InitPlan 3 (returns $2)"",
""Replicated"": ""no"",
""Node List"": [""datanode1"", ""datanode2""],
""Startup Cost"": 3625.12,
""Total Cost"": 3625.31,
""Plan Rows"": 19,
""Plan Width"": 8,
""Actual Startup Time"": 156.305,
""Actual Total Time"": 156.305,
""Actual Rows"": 0,
""Actual Loops"": 1,
""Output"": [""table_tag_2.id""],
""Shared Hit Blocks"": 0,
""Shared Read Blocks"": 0,
""Shared Dirtied Blocks"": 0,
""Shared Written Blocks"": 0,
""Local Hit Blocks"": 0,
""Local Read Blocks"": 0,
""Local Dirtied Blocks"": 0,
""Local Written Blocks"": 0,
""Temp Read Blocks"": 0,
""Temp Written Blocks"": 0
},
{
""Node Type"": ""Remote Subquery Scan"",
""Parent Relationship"": ""InitPlan"",
""Subplan Name"": ""InitPlan 4 (returns $3)"",
""Replicated"": ""no"",
""Node List"": [""datanode1"", ""datanode2""],
""Startup Cost"": 3625.12,
""Total Cost"": 3625.31,
""Plan Rows"": 19,
""Plan Width"": 8,
""Actual Startup Time"": 156.801,
""Actual Total Time"": 156.801,
""Actual Rows"": 0,
""Actual Loops"": 1,
""Output"": [""table_tag_3.id""],
""Shared Hit Blocks"": 0,
""Shared Read Blocks"": 0,
""Shared Dirtied Blocks"": 0,
""Shared Written Blocks"": 0,
""Local Hit Blocks"": 0,
""Local Read Blocks"": 0,
""Local Dirtied Blocks"": 0,
""Local Written Blocks"": 0,
""Temp Read Blocks"": 0,
""Temp Written Blocks"": 0
},
{
""Node Type"": ""Remote Subquery Scan"",
""Parent Relationship"": ""InitPlan"",
""Subplan Name"": ""InitPlan 5 (returns $4)"",
""Replicated"": ""no"",
""Node List"": [""datanode1"", ""datanode2""],
""Startup Cost"": 3625.12,
""Total Cost"": 3625.31,
""Plan Rows"": 19,
""Plan Width"": 8,
""Actual Startup Time"": 109.143,
""Actual Total Time"": 109.143,
""Actual Rows"": 0,
""Actual Loops"": 1,
""Output"": [""table_tag_4.id""],
""Shared Hit Blocks"": 0,
""Shared Read Blocks"": 0,
""Shared Dirtied Blocks"": 0,
""Shared Written Blocks"": 0,
""Local Hit Blocks"": 0,
""Local Read Blocks"": 0,
""Local Dirtied Blocks"": 0,
""Local Written Blocks"": 0,
""Temp Read Blocks"": 0,
""Temp Written Blocks"": 0
},
{
""Node Type"": ""Remote Subquery Scan"",
""Parent Relationship"": ""InitPlan"",
""Subplan Name"": ""InitPlan 6 (returns $5)"",
""Replicated"": ""no"",
""Node List"": [""datanode1"", ""datanode2""],
""Startup Cost"": 3625.12,
""Total Cost"": 3625.31,
""Plan Rows"": 19,
""Plan Width"": 8,
""Actual Startup Time"": 113.362,
""Actual Total Time"": 113.362,
""Actual Rows"": 0,
""Actual Loops"": 1,
""Output"": [""table_tag_5.id""],
""Shared Hit Blocks"": 0,
""Shared Read Blocks"": 0,
""Shared Dirtied Blocks"": 0,
""Shared Written Blocks"": 0,
""Local Hit Blocks"": 0,
""Local Read Blocks"": 0,
""Local Dirtied Blocks"": 0,
""Local Written Blocks"": 0,
""Temp Read Blocks"": 0,
""Temp Written Blocks"": 0
},
{
""Node Type"": ""Remote Subquery Scan"",
""Parent Relationship"": ""InitPlan"",
""Subplan Name"": ""InitPlan 7 (returns $6)"",
""Replicated"": ""no"",
""Node List"": [""datanode1"", ""datanode2""],
""Startup Cost"": 3625.12,
""Total Cost"": 3625.31,
""Plan Rows"": 19,
""Plan Width"": 8,
""Actual Startup Time"": 106.815,
""Actual Total Time"": 106.815,
""Actual Rows"": 0,
""Actual Loops"": 1,
""Output"": [""table_tag_6.id""],
""Shared Hit Blocks"": 0,
""Shared Read Blocks"": 0,
""Shared Dirtied Blocks"": 0,
""Shared Written Blocks"": 0,
""Local Hit Blocks"": 0,
""Local Read Blocks"": 0,
""Local Dirtied Blocks"": 0,
""Local Written Blocks"": 0,
""Temp Read Blocks"": 0,
""Temp Written Blocks"": 0
},
{
""Node Type"": ""Remote Subquery Scan"",
""Parent Relationship"": ""InitPlan"",
""Subplan Name"": ""InitPlan 8 (returns $7)"",
""Replicated"": ""no"",
""Node List"": [""datanode1"", ""datanode2""],
""Startup Cost"": 3625.12,
""Total Cost"": 3625.31,
""Plan Rows"": 19,
""Plan Width"": 8,
""Actual Startup Time"": 101.051,
""Actual Total Time"": 101.051,
""Actual Rows"": 0,
""Actual Loops"": 1,
""Output"": [""table_tag_7.id""],
""Shared Hit Blocks"": 0,
""Shared Read Blocks"": 0,
""Shared Dirtied Blocks"": 0,
""Shared Written Blocks"": 0,
""Local Hit Blocks"": 0,
""Local Read Blocks"": 0,
""Local Dirtied Blocks"": 0,
""Local Written Blocks"": 0,
""Temp Read Blocks"": 0,
""Temp Written Blocks"": 0
},
{
""Node Type"": ""Remote Subquery Scan"",
""Parent Relationship"": ""InitPlan"",
""Subplan Name"": ""InitPlan 9 (returns $8)"",
""Replicated"": ""no"",
""Node List"": [""datanode1"", ""datanode2""],
""Startup Cost"": 3625.12,
""Total Cost"": 3625.31,
""Plan Rows"": 19,
""Plan Width"": 8,
""Actual Startup Time"": 90.132,
""Actual Total Time"": 90.132,
""Actual Rows"": 0,
""Actual Loops"": 1,
""Output"": [""table_tag_8.id""],
""Shared Hit Blocks"": 0,
""Shared Read Blocks"": 0,
""Shared Dirtied Blocks"": 0,
""Shared Written Blocks"": 0,
""Local Hit Blocks"": 0,
""Local Read Blocks"": 0,
""Local Dirtied Blocks"": 0,
""Local Written Blocks"": 0,
""Temp Read Blocks"": 0,
""Temp Written Blocks"": 0
},
{
""Node Type"": ""Remote Subquery Scan"",
""Parent Relationship"": ""InitPlan"",
""Subplan Name"": ""InitPlan 10 (returns $9)"",
""Replicated"": ""no"",
""Node List"": [""datanode1"", ""datanode2""],
""Startup Cost"": 3625.12,
""Total Cost"": 3625.31,
""Plan Rows"": 19,
""Plan Width"": 8,
""Actual Startup Time"": 87.756,
""Actual Total Time"": 87.756,
""Actual Rows"": 0,
""Actual Loops"": 1,
""Output"": [""table_tag_9.id""],
""Shared Hit Blocks"": 0,
""Shared Read Blocks"": 0,
""Shared Dirtied Blocks"": 0,
""Shared Written Blocks"": 0,
""Local Hit Blocks"": 0,
""Local Read Blocks"": 0,
""Local Dirtied Blocks"": 0,
""Local Written Blocks"": 0,
""Temp Read Blocks"": 0,
""Temp Written Blocks"": 0
},
{
""Node Type"": ""Remote Subquery Scan"",
""Parent Relationship"": ""Outer"",
""Replicated"": ""no"",
""Node List"": [""datanode1"", ""datanode2""],
""Startup Cost"": 26.16,
""Total Cost"": 26.17,
""Plan Rows"": 1,
""Plan Width"": 384,
""Actual Startup Time"": 4880.839,
""Actual Total Time"": 4880.839,
""Actual Rows"": 0,
""Actual Loops"": 1,
""Output"": [""table_video.id"", ""table_video.name"", ""table_video.date_time"", ""table_video.remarks"", ""table_video.source_site"", ""table_video.pic"", ""table_video.language_text"", ""table_video.play_url"", ""table_video.updated"", ""table_video.type_id"", ""table_video.tags"", ""table_video.duration"", ""table_video.media_url"", ""table_video.category_ids"", ""table_video.tag_ids""],
""Shared Hit Blocks"": 3,
""Shared Read Blocks"": 0,
""Shared Dirtied Blocks"": 0,
""Shared Written Blocks"": 0,
""Local Hit Blocks"": 0,
""Local Read Blocks"": 0,
""Local Dirtied Blocks"": 0,
""Local Written Blocks"": 0,
""Temp Read Blocks"": 0,
""Temp Written Blocks"": 0
}
]
},
""Planning Time"": 3.243,
""Triggers"": [
],
""Execution Time"": 4909.801
}
]"
JSON ist nicht so prall einfach lesbar, normal TEXT wäre besser. Dazu Tabellendefinition und Indexe.
"Limit (cost=36279.29..36279.29 rows=1 width=384) (actual time=3964.703..3964.703 rows=0 loops=1)"
" Output: table_video.id, table_video.name, table_video.date_time, table_video.remarks, table_video.source_site, table_video.pic, table_video.language_text, table_video.play_url, table_video.updated, table_video.type_id, table_video.tags, table_video.durat (...)"
" InitPlan 1 (returns $0)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=74.087..88.971 rows=380 loops=1)"
" Output: table_tag.id"
" InitPlan 2 (returns $1)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=71.243..71.249 rows=1 loops=1)"
" Output: table_tag_1.id"
" InitPlan 3 (returns $2)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=60.752..60.752 rows=0 loops=1)"
" Output: table_tag_2.id"
" InitPlan 4 (returns $3)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=66.274..66.274 rows=0 loops=1)"
" Output: table_tag_3.id"
" InitPlan 5 (returns $4)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=63.579..63.579 rows=0 loops=1)"
" Output: table_tag_4.id"
" InitPlan 6 (returns $5)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=66.021..66.021 rows=0 loops=1)"
" Output: table_tag_5.id"
" InitPlan 7 (returns $6)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=62.027..62.027 rows=0 loops=1)"
" Output: table_tag_6.id"
" InitPlan 8 (returns $7)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=64.780..64.780 rows=0 loops=1)"
" Output: table_tag_7.id"
" InitPlan 9 (returns $8)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=69.940..69.940 rows=0 loops=1)"
" Output: table_tag_8.id"
" InitPlan 10 (returns $9)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=68.674..68.674 rows=0 loops=1)"
" Output: table_tag_9.id"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=26.16..26.17 rows=1 width=384) (actual time=3964.700..3964.700 rows=0 loops=1)"
" Output: table_video.id, table_video.name, table_video.date_time, table_video.remarks, table_video.source_site, table_video.pic, table_video.language_text, table_video.play_url, table_video.updated, table_video.type_id, table_video.tags, table_video (...)"
"Planning time: 2.450 ms"
"Execution time: 3995.721 ms"
Die verknüpfte Tag Tabelle ist recht einfach, allerdings enthält sie mehr als 200,000 Einträge.
CREATE TABLE tag
(
id bigint NOT NULL DEFAULT nextval('tag_id_seq'::regclass), -- id
tag_name text, -- name
status integer --aktiviert?
)
Die ganzen is not null Bedingungen sind überflüssig.
Eine Bedingungen wie ARRAY(select id FROM table_tag WHERE tag_name ... is vermutlich effizienter als EXIST Bedingung geschrieben:
and exists (select *
from tag_table tt
where tt.tag_name ILIKE '%bild.de%'
and tt.tag_id = any(table_video.tag_ids))
Um das ILIKE zu beschleunigen wäre ein trigram index auf der remarks spalte hilfreich.
Das JSON Format des execution plan ist nicht dafür gedacht von Menschen gelesen zu werden. ein explain (buffers, analyze, format text) ist da hilfreicher. Und am besten auch mit einem “standard Postgres” nicht mit XL gemacht.
Mich würde es nicht wundern, wenn ein “standard” Postgres 14 auf einem Rechner mit der deutlich verbesserten Unterstützung von parallelen Queries sogar schneller ist, als die doch etwas in die Tage gekommen Version 10 auf der das letzte XL Release beruht auf mehreren Rechnern.
IS NOT NOLL ist obligatorisch, sonst ist die Suche nicht mehr genau.
Ich habe da ca. ein Monat rumexperimentiert.
Nein, ist es nicht.
Der Ausdruck remarks IS NOT NULL AND remarks ILIKE '%musik%' liefert exakt die gleichen Ergebnisse wie remarks ILIKE '%musik%'
Ich denke die ganzen Bedingungen kann man auch etwas einfacher schreiben, sowas in der Art:
where (remarks ilike any (array['%impf%', '%schäden%', '%nebenwirkung%', '%adversive%'])
OR name ilike any (array['%impf%', '%schäden%', '%nebenwirkung%', '%adversive%']))
and not (remarks ilike any (array['%game%', '%musik%', '%gwup%', '%english%', '%bild.de%', '%letsplay%'])
and not (name ilike any (array['%game%', '%musik%', '%gwup%', '%english%', '%bild.de%', '%letsplay%'])
Hier ohne IS NOT NULL
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT TEXT)
SELECT * FROM table_video WHERE NOT (((((( (remarks ILIKE '%game%' OR table_video.name IS NOT NULL AND table_video.name ILIKE '%game%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%game%' GROUP BY id)::bigint[] && tag_ids ))
OR ( remarks IS NOT NULL AND remarks ILIKE '%musik%' OR table_video.name ILIKE '%musik%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%musik%' GROUP BY id)::bigint[] && tag_ids ))
OR (remarks ILIKE '%gwup%' OR table_video.name ILIKE '%gwup%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%gwup%' GROUP BY id)::bigint[] && tag_ids ))
OR ( remarks ILIKE '%englisch%' OR table_video.name ILIKE '%englisch%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%englisch%' GROUP BY id)::bigint[] && tag_ids ))
OR ( remarks ILIKE '%bild.de%' OR table_video.name ILIKE '%bild.de%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%bild.de%' GROUP BY id)::bigint[] && tag_ids ))
OR ( remarks ILIKE '%letsplay%' OR table_video.name ILIKE '%letsplay%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%letsplay%' GROUP BY id)::bigint[] && tag_ids ))
AND (( ( remarks ILIKE '%impf%' OR table_video.name ILIKE '%impf%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%impf%' GROUP BY id)::bigint[] && tag_ids ))
AND ( remarks ILIKE '%schäden%' OR table_video.name ILIKE '%schäden%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%schäden%' GROUP BY id)::bigint[] && tag_ids OR remarks ILIKE '%nebenwirkung%' OR table_video.name ILIKE
'%nebenwirkung%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%nebenwirkung%' GROUP BY id)::bigint[] && tag_ids OR remarks ILIKE '%adversive%' OR table_video.name ILIKE '%adversive%' OR ARRAY(select id FROM table_tag WHERE tag_name ILIKE '%adversive%' GROUP BY id)::bigint[] && tag_ids )) ORDER BY ID DESC LIMIT 38
"Limit (cost=36279.29..36279.29 rows=1 width=384) (actual time=5378.489..5378.489 rows=0 loops=1)"
" Output: table_video.id, table_video.name, table_video.date_time, table_video.remarks, table_video.source_site, table_video.pic, table_video.language_text, table_video.play_url, table_video.updated, table_video.type_id, table_video.tags, table_video.durat (...)"
" InitPlan 1 (returns $0)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=96.264..157.311 rows=380 loops=1)"
" Output: table_tag.id"
" InitPlan 2 (returns $1)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=71.959..158.037 rows=1 loops=1)"
" Output: table_tag_1.id"
" InitPlan 3 (returns $2)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=154.882..154.882 rows=0 loops=1)"
" Output: table_tag_2.id"
" InitPlan 4 (returns $3)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=169.428..169.428 rows=0 loops=1)"
" Output: table_tag_3.id"
" InitPlan 5 (returns $4)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=148.493..148.493 rows=0 loops=1)"
" Output: table_tag_4.id"
" InitPlan 6 (returns $5)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=111.734..111.734 rows=0 loops=1)"
" Output: table_tag_5.id"
" InitPlan 7 (returns $6)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=105.156..105.156 rows=0 loops=1)"
" Output: table_tag_6.id"
" InitPlan 8 (returns $7)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=111.514..111.514 rows=0 loops=1)"
" Output: table_tag_7.id"
" InitPlan 9 (returns $8)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=119.056..119.056 rows=0 loops=1)"
" Output: table_tag_8.id"
" InitPlan 10 (returns $9)"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=3625.12..3625.31 rows=19 width=8) (actual time=92.630..92.630 rows=0 loops=1)"
" Output: table_tag_9.id"
" -> Remote Subquery Scan on all (datanode1,datanode2) (cost=26.16..26.17 rows=1 width=384) (actual time=5378.487..5378.487 rows=0 loops=1)"
" Output: table_video.id, table_video.name, table_video.date_time, table_video.remarks, table_video.source_site, table_video.pic, table_video.language_text, table_video.play_url, table_video.updated, table_video.type_id, table_video.tags, table_video (...)"
"Planning time: 2.498 ms"
"Execution time: 5408.855 ms"
Der Flaschenhals befindet sich beim Suchen in den Tags.
Ich habe bei der Suche noch eine weitere Verknüpfung zu einer Tabelle Kategorie, die ich wegen Überlastung abschalten musste.
Wie gesagt, die Tag Tabelle hat über 200,000 Einträge und die Haupttabelle 2,000,000 Einträge.
Wie gesagt: eine EXISTS Bedingung auf die TAGS Tabelle zusammen mit trigram Indizes auf die Spalten mit ILIKE Bedingungen sollten helfen. Das GROUP BY in den ARRAY(select...) für die Tag IDs ist im übrigen auch überflüssig. Da alle Bedingungen mit OR verknüpft sind, kann man alle Tags mit einer einzelnen EXISTS Bedingung abfragen und in dem EXISTS Sub-Query auch mit ilike any (...) arbeiten.
Kombiniere das ganze mit der deutlich verbesserten Unterstützung für parallele Abfragen in Postgres 14 (statt der veralteten 10er Version), dann kommst Du vermutlich auch auf einem (anständig ausgerüsteten) Server zu ausreichend guten Ergebnissen. Das könnte auch eine Abfrage sein, bei der jit unter Umständen hilfreich ist.
Warum brauchst Du eigentlich das ILIKE bei den Tags? Ich kenne das so, dass man die Tags als einzelne Worte speichert und dann mit = prüft.
Naja, wenn ich Musik auschließen möchte, soll auch Elektromusik ausgeschlossen werden.
Oder wenn ich Youtube auschliese, dann soll auch Youtuber ausgeschlossen werden, etc…
Auch gibt es die möglichkeit mit | zu arbeiten, ich kann also nicht einfach alles in ein Array packen.
Mein lokaler Rechnerverbund besteht aus 2 Rechnern, der Hauptrechner hat 5,000 Euro gekostet:
...
processor : 22
vendor_id : GenuineIntel
cpu family : 6
model : 63
model name : Intel(R) Xeon(R) CPU E5-2690 v3 @ 2.60GHz
stepping : 2
microcode : 0x3c
cpu MHz : 1199.865
cache size : 30720 KB
physical id : 0
siblings : 24
core id : 12
cpu cores : 12
apicid : 25
initial apicid : 25
fpu : yes
fpu_exception : yes
cpuid level : 15
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm epb invpcid_single ibrs ibpb stibp kaiser tpr_shadow vnmi flexpriority ept vpid fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid cqm xsaveopt cqm_llc cqm_occup_llc dtherm ida arat pln pts
bugs : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass l1tf mds swapgs itlb_multihit
bogomips : 5199.93
clflush size : 64
cache_alignment : 64
address sizes : 46 bits physical, 48 bits virtual
power management:
processor : 23
vendor_id : GenuineIntel
cpu family : 6
model : 63
model name : Intel(R) Xeon(R) CPU E5-2690 v3 @ 2.60GHz
stepping : 2
microcode : 0x3c
cpu MHz : 1199.865
cache size : 30720 KB
physical id : 0
siblings : 24
core id : 13
cpu cores : 12
apicid : 27
initial apicid : 27
fpu : yes
fpu_exception : yes
cpuid level : 15
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm epb invpcid_single ibrs ibpb stibp kaiser tpr_shadow vnmi flexpriority ept vpid fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid cqm xsaveopt cqm_llc cqm_occup_llc dtherm ida arat pln pts
bugs : cpu_meltdown spectre_v1 spectre_v2 spec_store_bypass l1tf mds swapgs itlb_multihit
bogomips : 5199.93
clflush size : 64
cache_alignment : 64
address sizes : 46 bits physical, 48 bits virtual
power management: