Postgres-xl currval of sequence

Wie gesagt: ein explain (analyze, buffers) Deiner Abfrage mit einer aktuellen Postgres 14 Version (anstatt dem explain von Postgres-XL) wäre hilfreich um noch weitere Vorschläge zu machen. Mit 24 CPUs solltest Du auf jeden Fall die Standard Einstellungen für parallele Anfragen hochdrehen, damit Du das auch ausnutzen kannst (ich weiß gar nicht ob Postgres-XL auf einem Knoten überhaupt parallel arbeitet)

Nur als Zusammenfassung was ich versuchen würde:

  • kombinieren die Werten die Du suchst mit einer = ANY(...) Bedingung pro Spalte
  • verwende eine EXISTS Bedingung für alle Tags die eingeschlossen sind. Alternativ könnte es auch viel bringen zuerst alle relevanten IDs der Tags zu ermitteln und dann als Parameter in der Abfrage zu verwenden.
  • verwende eine NOT EXISTS Bedingung für alle Tags die ausgeschlossen werden sollen.
  • lege Trigram Indizes auf die text Spalten an

Wenn Du wirklich meinst Du brauchst mehr als einen Knoten, dann wäre Greenplum vermutlich die bessere Lösung. Das ist aktuell und wird auch gepflegt und ist stärker auf MPP ausgerichtet als XL.

Danke dir mein Lieber!

Ich arbeite mich mal in Greenplum ein.

Ich habe nämlich noch 1TB Bücher, die darauf warten eingespeißt werden und mit einer exakten Volltext-Suche dem Benutzer angezeigt werden wollen.

Wenn Du eine Volltextsuche willst, warum hast Du die noch nicht versucht?

Soweit bin ich noch nicht, ich muss es erst einmal auf die Reihe bekommen,
die Suche (Titel, Beschreibung, Tag und Kategegorie) unter eine Sekunde zubekommen. Im Moment bin ich leider noch bei 200 Sekunden.

Neben der Suche werden ja dann noch Daten von ca. 100 Spinnen eingespeißt.

Naja, vielleicht ist die Volltextsuche ja die Lösung. Ich würde das erstmal ausprobieren.

Aber so lange Du für jede Bedingung bei den Tags eine Unterabfrage verwendest, kannst Du soviel probieren wie Du willst, das wird auch auf einem verteilten System nicht wirklich schneller werden. Und ohne trigram Index werden auch die ILIKE Bedingungen nie wirklich schnell werden.

Und OR Verknüpfungen führen fast immer zu langsamen Abfragen, deswegen wäre es sinnvoller mit ilike any (...) zu arbeiten.

trigram Index gitbt es in Postgresl-XL 10 glaube ich nicht.
Ich versuche mal auf greenplum zumzusteigen, danke Dir!

Wie gesagt: mit den richtigen Indizes (trigram und/oder Volltext) + ausreichend dimensionierte parallel queries sollte das auch mit einem “normalen” Postgres 14 machbar sein (vor allem wenn die eine Maschine in der Summe genauso viel CPUs hat wie der Verbund aus zwei Maschinen).

Aber wenn Du Dir das Leben unnötig kompliziert machen willst, bitte.

Ich kann leider kein Postgres 14 installieren, die höchste Version, die ich installieren kann ist Postgres 10.

Außerdem, werden die Daten immer mehr, da reicht ein Server nicht aus, Auch mit wöchentlichten Booster-Impfungen von AstraZenica und Sputnik nicht.

Warum? Ist Dir klar, dass Postgres 10 in etwa 6 Monaten nicht mehr unterstützt wird?

Heute mit einem neuen Projekt auf Postgres 10 Basis anzufangen halte ich für fahrlässig.

Wenn mehr Daten reinkommen, dann musst Du halt mehr Platten verwenden. Ein RAID Array mit SSDs ermöglicht weitaus effizientere I/O Parallelität als das mit mehrere Servern möglich ist. Und parallel Queries auf einem Rechner mit 64 CPUs ist auch schneller als Parallelverarbeitung auf zwei Servern mit je 32 CPUs

verstehe.

Die Systeme, die wir verwenden sind aus Sicherheits- und Kompatiblitätsgründen etwas älter.
Da gibt es so manches Paket, das etwas älter ist.

Aus “Sicherheitsgründen” Software einsetzen die keine Security-Fixes bekommt - finde den Fehler.

1 „Gefällt mir“

Es gibt kein großen Fehler. Es gibt ja Sicherheitsupdate.

Für XL gibt es keine regelmäßigen Sicherheitsupdates.
Und für Postgres 10 wird es ab November keine mehr geben.

Danke für die Info.
Ich steige gerade auf greenplum um, leider ist es etwas komplizierter, wegen Kompatiblitätsproblemen.

Hallo @castorp,

Respekt, das du immer sachlich und beim Thema geblieben bist. :+1:
Ich zumindest habe mir ein paar Anregungen mitnehmen können.

1 „Gefällt mir“

Hier ist mit Verlaub schon der Ansatz suboptimal.

Der wesentliche Ansatz für eine performant funktionierende Lösung ist immer, lineare Tablescans zu vermeiden. Sprich: Wildcards am Anfang von LIKE sind immer langsam.

Ein klassischer Ansatz für das diskutierte Problem wäre das Normalisieren der Tags und Indizieren über eine Fragmenttabelle, die z.B. alle in den Tags vorkommenden 4-Zeichen-Folgen als Schlüsselfeld enthält.

Oder man benutzt gleich eine der vielen Varianten von Volltext-Index-Lösungen, die im Grunde nichts anderes sind als eine Kapselung davon, evtl. noch kombiniert mit einer Silbenbibliothek.

Außerdem ist es bei den meisten RDBMS performanter, anstatt EXISTS/NOT EXISTS einen LEFT JOIN zu machen und die Schlüsselspalte der rechten Tabelle auf NULL oder NOT NULL zu prüfen. Damit faßt man die rechte Tabelle nämlich nur einmal an und nicht bei jedem Datensatz.

Beispiel (Pseudo-Code, untested):

SELECT KEY
FROM CONTENT
INNER JOIN TAGGING
ON TAGGING.CONTENT_KEY = CONTENT.KEY

INNER JOIN TAG
ON TAGGING.TAG_KEY = TAG.KEY

LEFT JOIN FRAGMENT 
   ON FRAGMENT.TAG_KEY = TAG.KEY
 AND FRAGMENT.FRAGMENT IN (generate_fragments($SUCHBEGRIFF))

WHERE FRAGMENT.TAG_KEY IS NOT NULL

generate_fragments() erzeugt einfach alle möglichen Fragmente aus einem String. Natürlich muß man die FRAGMENT-Tabelle mitpflegen, entweder “händisch” oder mit einem INSERT-Trigger auf TAG oder wenn das RDBMS das hergibt vielleicht sogar ganz elegant materialisierte View auf TAG, die generate_fragments() aufruft und dann auf der Fragmentspalte indiziert wird. Wenn das RDBMS keine incremental refreshes auf materialized views kann muß man aber aufpassen wie oft man die wirklich refreshed und neue Tags werden halt immer erst nach dem nächsten Refresh gefunden.

Disclaimer 1: ob LEFT JOIN … WHERE FOREIGN_KEY IS NOT NULL bei POSTGRESQL auch schneller ist als EXISTS oder NOT EXISTS weiß ich nicht, mir ist nicht bekannt wie gut der Optimizer sowas erkennt und wegoptimiert. Beim SQL-Server wars bis jetzt immer schneller, bei Oracle meistens…

Disclaimer 2: den Stunt mit der Fragmenttabelle müßte man sich heutzutage eigentlich durch irgendeine Form von FULLTEXT Index sparen können. Da gibt das postgresql-Universum mehrere Varianten her.

In jedem Fall wird eine solche Lösung besser skalieren und performen als lineare Table Scans. Solange die Fragmenttabelle kleiner als ein paar GB bleibt braucht man sich da über den Query Speed sicher keine Sorgen zu machen.

Dabei kann man die Fragmenttabelle sehr klein halten, die muß ja nur den Tag-Key und das Fragment enthalten (achtung: deren Primärschlüssel geht über beide Spalten, denn ein Fragment kann in mehreren Tags vorhanden sein!).

Grundsätzlich gebe ich Dir Recht. Mich hat es auch gewundert, dass man Tags mit Wildcard sucht.

Aber das immer stimmt nicht. In Postgres kann man einen trigram Index anlegen, der dann auch von einer Bedingung wie where tag ilike '%bla%' verwendet wird. Da die Volltextsuche nicht unbedingt gut geeignet ist für eine Teilwortsuche (also das was LIKE macht), ist das vielleicht sogar die bessere Alternative in diesem Fall.

Die Variante die Tags zu normalisieren, so dass man mit = (oder IN) arbeiten kann, ist aber vermutlich insgesamt schneller.

Postgres ist bei der Variante mit LEFT JOIN etwas empfindlicher welche Spalte man dann auf NULL prüft (für ein NOT EXISTS äquivalent). Wenn man das IS NOT NULL auf der FK Spalte macht geht’s schnell:

select count(c.id)
from customers c
  left join orders o on o.customer_id = c.id
where o.customer_id is null;

Dazugehöriger Plan ist:

QUERY PLAN                                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=50352.17..50352.18 rows=1 width=8) (actual time=147.754..147.755 rows=1 loops=1)                                                           
  Output: count(*)                                                                                                                                          
  Buffers: shared hit=300850                                                                                                                                
  ->  Nested Loop Anti Join  (cost=0.43..50286.63 rows=26213 width=0) (actual time=147.752..147.752 rows=0 loops=1)                                         
        Buffers: shared hit=300850                                                                                                                          
        ->  Seq Scan on stuff.customers c  (cost=0.00..1849.00 rows=100000 width=4) (actual time=0.011..6.295 rows=100000 loops=1)                          
              Output: c.id, c.customer_name, c.zip_code, c.city                                                                                             
              Buffers: shared hit=849                                                                                                                       
        ->  Index Only Scan using orders_customer_id_idx on stuff.orders o  (cost=0.43..1.27 rows=36 width=4) (actual time=0.001..0.001 rows=1 loops=100000)
              Output: o.customer_id                                                                                                                         
              Index Cond: (o.customer_id = c.id)                                                                                                            
              Heap Fetches: 0                                                                                                                               
              Buffers: shared hit=300001                                                                                                                    
Settings: max_parallel_workers_per_gather = '0'
Planning:                                                                                                                                                   
  Buffers: shared hit=14                                                                                                                                    
Planning Time: 0.286 ms                                                                                                                                     
Execution Time: 147.790 ms                                                                                                                                  

Aber das hier ist super langsam:

select count(c.id)
from customers c
  left join orders o on o.customer_id = c.id
where o.id is null;

Dazu gehöriger Plan ist:

QUERY PLAN                                                                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=74233.43..74233.44 rows=1 width=8) (actual time=1414.097..1414.099 rows=1 loops=1)                                      
  Output: count(*)                                                                                                                       
  Buffers: shared hit=17565, temp read=4633 written=4633                                                                                 
  ->  Hash Right Join  (cost=3490.00..74233.43 rows=1 width=0) (actual time=1414.093..1414.095 rows=0 loops=1)                           
        Inner Unique: true                                                                                                               
        Hash Cond: (o.customer_id = c.id)                                                                                                
        Filter: (o.id IS NULL)                                                                                                           
        Rows Removed by Filter: 2624378                                                                                                  
        Buffers: shared hit=17565, temp read=4633 written=4633                                                                           
        ->  Seq Scan on stuff.orders o  (cost=0.00..42959.28 rows=2624328 width=8) (actual time=0.020..335.147 rows=2624378 loops=1)     
              Output: o.customer_id, o.id                                                                                                
              Buffers: shared hit=16716                                                                                                  
        ->  Hash  (cost=1849.00..1849.00 rows=100000 width=4) (actual time=29.174..29.175 rows=100000 loops=1)                           
              Output: c.id                                                                                                               
              Buckets: 131072  Batches: 2  Memory Usage: 2781kB                                                                          
              Buffers: shared hit=849, temp written=146                                                                                  
              ->  Seq Scan on stuff.customers c  (cost=0.00..1849.00 rows=100000 width=4) (actual time=0.012..10.705 rows=100000 loops=1)
                    Output: c.id                                                                                                         
                    Buffers: shared hit=849                                                                                              
Settings: max_parallel_workers_per_gather = '0'
Planning:                                                                                                                                
  Buffers: shared hit=17                                                                                                                 
Planning Time: 0.228 ms                                                                                                                  
Execution Time: 1414.453 ms                                                                                                              

Die NOT EXISTS Variante hat das Problem nicht, da man da grundsätzlich über die FK Spalte gehen muss. Dazu gehöriger Plan ist identisch zur ersten Variante (mit o.customer_id is null):

QUERY PLAN                                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate  (cost=50352.17..50352.18 rows=1 width=8) (actual time=147.777..147.779 rows=1 loops=1)                                                           
  Output: count(*)                                                                                                                                          
  Buffers: shared hit=300850                                                                                                                                
  ->  Nested Loop Anti Join  (cost=0.43..50286.63 rows=26213 width=0) (actual time=147.773..147.774 rows=0 loops=1)                                         
        Buffers: shared hit=300850                                                                                                                          
        ->  Seq Scan on stuff.customers c  (cost=0.00..1849.00 rows=100000 width=4) (actual time=0.015..6.011 rows=100000 loops=1)                          
              Output: c.id, c.customer_name, c.zip_code, c.city                                                                                             
              Buffers: shared hit=849                                                                                                                       
        ->  Index Only Scan using orders_customer_id_idx on stuff.orders o  (cost=0.43..1.27 rows=36 width=4) (actual time=0.001..0.001 rows=1 loops=100000)
              Output: o.customer_id                                                                                                                         
              Index Cond: (o.customer_id = c.id)                                                                                                            
              Heap Fetches: 0                                                                                                                               
              Buffers: shared hit=300001                                                                                                                    
Settings: max_parallel_workers_per_gather = '0'
Planning:                                                                                                                                                   
  Buffers: shared hit=14                                                                                                                                    
Planning Time: 0.283 ms                                                                                                                                     
Execution Time: 147.825 ms                                                                                                                                  

Oracle verwendet für NOT EXISTS und LEFT JOIN mit “fk column is null” absolut identische Pläne. Für die Variante “pk column is null” einen schlechteren Plan weil dabei der FK Index nicht verwendet wird.

select count(*)
from customers c
where not exists (select * 
                  from orders o
                  where o.customer_id = c.id);
                  
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |      1 |        |       |       |  2033 (100)|      1 |00:00:00.16 |    3143 |       |       |          |
|   1 |  SORT AGGREGATE        |              |      1 |      1 |    18 |       |            |      1 |00:00:00.16 |    3143 |       |       |          |
|*  2 |   HASH JOIN ANTI       |              |      1 |  89775 |  1578K|  2192K|  2033   (2)|      0 |00:00:00.16 |    3143 |  6597K|  3201K| 6991K (0)|
|   3 |    INDEX FAST FULL SCAN| SYS_C0013196 |      1 |  89775 |  1139K|       |    68   (0)|    100K|00:00:00.01 |     251 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| ORD_CUST_ID  |      1 |   1298K|  6340K|       |   793   (2)|   1298K|00:00:00.07 |    2892 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------


select count(*)
from customers c
  left join orders o on o.customer_id = c.id
where o.customer_id is null;


---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |      1 |        |       |       |  2033 (100)|      1 |00:00:00.18 |    3143 |       |       |          |
|   1 |  SORT AGGREGATE        |              |      1 |      1 |    18 |       |            |      1 |00:00:00.18 |    3143 |       |       |          |
|*  2 |   HASH JOIN ANTI       |              |      1 |  89775 |  1578K|  2192K|  2033   (2)|      0 |00:00:00.18 |    3143 |  6597K|  3201K| 6992K (0)|
|   3 |    INDEX FAST FULL SCAN| SYS_C0013196 |      1 |  89775 |  1139K|       |    68   (0)|    100K|00:00:00.01 |     251 |       |       |          |
|   4 |    INDEX FAST FULL SCAN| ORD_CUST_ID  |      1 |   1298K|  6340K|       |   793   (2)|   1298K|00:00:00.09 |    2892 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------

select count(*)
from customers c
  left join orders o on o.customer_id = c.id
where o.id is null;

----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name         | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |              |      1 |        |       |       |  3026 (100)|      1 |00:00:00.10 |    5311 |       |       |          |
|   1 |  SORT AGGREGATE         |              |      1 |      1 |    24 |       |            |      1 |00:00:00.10 |    5311 |       |       |          |
|*  2 |   FILTER                |              |      1 |        |       |       |            |      0 |00:00:00.10 |    5311 |       |       |          |
|*  3 |    HASH JOIN OUTER      |              |      1 |  89775 |  2104K|  2192K|  3026   (2)|   1298K|00:00:00.10 |    5311 |  6597K|  3201K| 6914K (0)|
|   4 |     INDEX FAST FULL SCAN| SYS_C0013196 |      1 |  89775 |  1139K|       |    68   (0)|    100K|00:00:00.01 |     251 |       |       |          |
|   5 |     TABLE ACCESS FULL   | ORDERS       |      1 |   1298K|    13M|       |  1418   (2)|   1298K|00:00:00.03 |    5060 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------

Also zumindest für Postgres & Oracle ist die NOT EXISTS Variante damit die “sicherere Wahl” weil man bei der Bedingung nichts falsch machen kann.

1 „Gefällt mir“

Wie soll ich denn die Tag ID wissen, wenn ich nicht danach suche?
Tag Beispiele:
10 Black Metal
11 Heavy Metal
12 Metal

Entweder suchst Du nach dem kompletten Tag. Dann brauchst Du kein Like. Oder Du benutzt irgendeine der diversen Vollext-Indizierungsmethoden. Da würde ich jetzt auch mal Trigraph-Indices (die hier durchaus geeignet sein könnten) drunter fallen. Oder wie umrissen quasi “von Hand” mit den guten alten Fragmenttabellen - die Technik kannten WaWi-Entwickler schon in den 1980ern als die noch überwiegend mit ISAM-Dateien, FABS und dergleichen rumgewurstelt haben. Nicht schön, aber durchaus performant.